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

From: Christophe Pettus <[email-masked]>
Date: Sat, 22 Mar 2025 09:01:51 +0000

Hello,

> On Mar 22, 2025, at 08:38, Kevin Stephenson <[email-masked]> 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.

---

From: Tom Lane <[email-masked]>
Date: Sat, 22 Mar 2025 10:59:10 -0400

> 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 wi…

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

---

From: Kevin Stephenson <[email-masked]>
Date: Sat, 22 Mar 2025 21:37:53 +0000

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-masked]>
Sent: Saturday, March 22, 2025 7:59 AM
To: Christophe Pettus <[email-masked]>
Cc: Kevin Stephenson <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000

                        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>

---

From: Christophe Pettus <[email-masked]>
Date: Sat, 22 Mar 2025 22:18:07 +0000

> On Mar 22, 2025, at 21:37, Kevin Stephenson <[email-masked]> wrote:
> 

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.

---

From: Tom Lane <[email-masked]>
Date: Sat, 22 Mar 2025 18:55:08 -0400

> 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…

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

---

From: Tom Lane <[email-masked]>
Date: Sat, 22 Mar 2025 21:12:05 -0400

> 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