Christophe and Tom, thank you for your responses, but I'm still a bit confused.
In my original email, the Test 2 case is allowing a ROLLBACK in the EXCEPTION
clause without throwing an error. Is it a NOP ROLLBACK being applied to an
aborted subTX, a real full ROLLBACK, or something else? Please advise.
Thanks,
Kevin Stephenson
________________________________
From: Tom Lane <t...@sss.pgh.pa.us>
Sent: Saturday, March 22, 2025 7:59 AM
To: Christophe Pettus <x...@thebuild.com>
Cc: Kevin Stephenson <kjs...@hotmail.com>; pgsql-gene...@postgresql.org
<pgsql-gene...@postgresql.org>
Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination
2D000
Christophe Pettus <x...@thebuild.com> writes:
> A procedure cannot issue top-level transaction control statements from within
> an exception block, and attempting to do so raises the error you saw. This
> includes procedures that are called from within an exception block.
Yeah. Postgres doesn't have autonomous transactions (not yet anyway),
and you can't fake them like that.
A way that does work, I believe, is to set up a second session with
dblink[1] and use that to issue the autonomous transaction. Ugly
and inefficient for sure, but if you've gotta have it...
regards, tom lane
[1]
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fdblink.html&data=05%7C02%7C%7Ce846300d6b9c402760ec08dd69521aad%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638782523529471489%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=2Rn9iT1VcDJgCXesww3AcwD16UIWE3HsEgniD0Byodk%3D&reserved=0<https://www.postgresql.org/docs/current/dblink.html>
