Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51.11.1\))
Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination
 2D000
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2@IA0PR19MB7217.namprd19.prod.outlook.com>
Date: Sat, 22 Mar 2025 22:18:07 +0000
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <E2B9C6B4-4B79-43C6-9FF9-DED41E5CE9A5@thebuild.com>
References: <IA0PR19MB721723603709836EE5D0B17E8FDA2@IA0PR19MB7217.namprd19.prod.outlook.com>
 <12B83964-7B3B-4345-9952-F8FE61727CB5@thebuild.com>
 <1830668.1742655550@sss.pgh.pa.us>
 <IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2@IA0PR19MB7217.namprd19.prod.outlook.com>
To: Kevin Stephenson <kjs714@hotmail.com>
Archived-At: <https://www.postgresql.org/message-id/E2B9C6B4-4B79-43C6-9FF9-DED41E5CE9A5%40thebuild.com>
Precedence: bulk


> On Mar 22, 2025, at 21:37, Kevin Stephenson <kjs714@hotmail.com> =
wrote:
>=20
> 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=3D# 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=3D# create or replace procedure inner() as $$
BEGIN
   BEGIN
      INSERT INTO t VALUES(1);
      PERFORM 1/0;
   EXCEPTION WHEN OTHERS THEN
      ROLLBACK;                                       =20
      INSERT INTO t VALUES(2);
   END;
END;                =20
$$
language plpgsql;
CREATE PROCEDURE

xof=3D# call outer();
NOTICE:  in outer exception handler
CALL

xof=3D# table t;
 i =20
---
 3
(1 row)

xof=3D# truncate t;
TRUNCATE TABLE

xof=3D# call inner();
CALL

xof=3D# table t;
 i =20
---
 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.=