public inbox for [email protected]  

help / color / mirror / Atom feed

From: Christophe Pettus <[email protected]>
To: Kevin Stephenson <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000
Date: Sat, 22 Mar 2025 22:18:07 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2@IA0PR19MB7217.namprd19.prod.outlook.com>
References: <IA0PR19MB721723603709836EE5D0B17E8FDA2@IA0PR19MB7217.namprd19.prod.outlook.com>
	<[email protected]>
	<[email protected]>
	<IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2@IA0PR19MB7217.namprd19.prod.outlook.com>



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

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox