public inbox for [email protected]help / color / mirror / Atom feed
Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 6+ messages / 3 participants [nested] [flat]
* Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 @ 2025-03-22 09:01 Christophe Pettus <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Christophe Pettus @ 2025-03-22 09:01 UTC (permalink / raw) To: Kevin Stephenson <[email protected]>; +Cc: pgsql-general Hello, > On Mar 22, 2025, at 08:38, Kevin Stephenson <[email protected]> wrote: > • When a top-level stored procedure is called it implicitly creates a TX if there is no current TX. > • When a BEGIN/EXCEPTION block is used it implicitly creates a subTX for that block. These statements are correct. > • When a nested stored procedure is called it implicitly creates a subTX for that invocation. This one is not. (Although the behavior you are looking for may not depend on that.) 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 @ 2025-03-22 14:59 Tom Lane <[email protected]> parent: Christophe Pettus <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tom Lane @ 2025-03-22 14:59 UTC (permalink / raw) To: Christophe Pettus <[email protected]>; +Cc: Kevin Stephenson <[email protected]>; pgsql-general Christophe Pettus <[email protected]> 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://www.postgresql.org/docs/current/dblink.html ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 @ 2025-03-22 21:37 Kevin Stephenson <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: Kevin Stephenson @ 2025-03-22 21:37 UTC (permalink / raw) To: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; +Cc: pgsql-general 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 <[email protected]> Sent: Saturday, March 22, 2025 7:59 AM To: Christophe Pettus <[email protected]> Cc: Kevin Stephenson <[email protected]>; [email protected] <[email protected]> Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 Christophe Pettus <[email protected]> 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...; ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 @ 2025-03-22 22:18 Christophe Pettus <[email protected]> parent: Kevin Stephenson <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: Christophe Pettus @ 2025-03-22 22:18 UTC (permalink / raw) To: Kevin Stephenson <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-general > On Mar 22, 2025, at 21:37, Kevin Stephenson <[email protected]> wrote: > > 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. 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: xof=# CREATE OR REPLACE PROCEDURE outer() AS $$ BEGIN INSERT INTO t VALUES(3); BEGIN CALL inner(); PERFORM 1/0; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'in outer exception handler'; END; END; $$ language plpgsql; CREATE PROCEDURE xof=# create or replace procedure inner() as $$ BEGIN BEGIN INSERT INTO t VALUES(1); PERFORM 1/0; EXCEPTION WHEN OTHERS THEN ROLLBACK; INSERT INTO t VALUES(2); END; END; $$ language plpgsql; CREATE PROCEDURE xof=# call outer(); NOTICE: in outer exception handler CALL xof=# table t; i --- 3 (1 row) xof=# truncate t; TRUNCATE TABLE xof=# call inner(); CALL xof=# table t; i --- 2 (1 row) It clearly doesn't roll back the outer transaction. The savepoint that BEGIN ... EXCEPTION creates is released upon entry into the EXCEPTION block, so there's no savepoint in that context to roll back to. Pragmatically, the answer is: don't put top-level transaction control statements in procedures where they might be invoked within an EXCEPTION block, either directly or indirectly. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 @ 2025-03-22 22:55 Tom Lane <[email protected]> parent: Kevin Stephenson <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Tom Lane @ 2025-03-22 22:55 UTC (permalink / raw) To: Kevin Stephenson <[email protected]>; +Cc: Christophe Pettus <[email protected]>; pgsql-general Kevin Stephenson <[email protected]> writes: > 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. The sub-transaction only exists for the body of the BEGIN construct, up until EXCEPTION. By the time control arrives at an exception handler, we've rolled back the sub-xact and are executing in the outer transaction again. So if that's a top-level transaction, you can roll it back, but if it's a subtransaction you can't. regards, tom lane ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 @ 2025-03-23 01:12 Tom Lane <[email protected]> parent: Christophe Pettus <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2025-03-23 01:12 UTC (permalink / raw) To: Christophe Pettus <[email protected]>; +Cc: Kevin Stephenson <[email protected]>; pgsql-general Christophe Pettus <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-03-23 01:12 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-22 09:01 Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 Christophe Pettus <[email protected]> 2025-03-22 14:59 ` Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 Tom Lane <[email protected]> 2025-03-22 21:37 ` Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 Kevin Stephenson <[email protected]> 2025-03-22 22:18 ` Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 Christophe Pettus <[email protected]> 2025-03-23 01:12 ` Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 Tom Lane <[email protected]> 2025-03-22 22:55 ` Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox