Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000

Christophe Pettus <xof(at)thebuild(dot)com> writes:
> That's an interesting question. It appears to be a no-op, although a quick scan of the code doesn't reveal why. Here's an illustrative test case:

This test case would be less confusing if the outer handler did

RAISE NOTICE 'in outer exception handler: %', sqlerrm;

With that, the test shows

regression=# call outer();
NOTICE: in outer exception handler: invalid transaction termination
CALL

What is happening is that inner() does PERFORM 1/0, fails and bounces
out to its exception handler, and then the ROLLBACK throws an error
because we're still inside outer()'s subtransaction. So inner()'s
first INSERT has been rolled back and the second one is never
reached. Back at outer()'s exception handler, we trap the error
from ROLLBACK, abort that subtransaction, and go on our merry way,
allowing the original INSERT (which was outside both subtransactions)
to complete.

regards, tom lane