Can someone please explain to me why this worked?
What happened behind the scenes?
I suspect that when you catch exceptions inside of a LOOP and the code ends up generating an exception, Postgres can’t use cached plans to optimize that code so it ends up planning the code at each iteration and this causes performance issues.
Is my assumption correct?
Not sure how much detail you are looking for but the docs say this:
"Tip: A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need."
I'm somewhat doubting "plan caching" has anything to do with this; I suspect its basically that there is high memory and runtime overhead to deal with the possibilities of needing to convert a exception into a branch instead of allowing it to be fatal.
David J.
