From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Stephenson <kjs714@hotmail.com>
cc: Christophe Pettus <xof@thebuild.com>,
        "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000
In-reply-to: <IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2@IA0PR19MB7217.namprd19.prod.outlook.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>
Comments: In-reply-to Kevin Stephenson <kjs714@hotmail.com>
	message dated "Sat, 22 Mar 2025 21:37:53 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <442117.1742684108.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 22 Mar 2025 18:55:08 -0400
Message-ID: <442118.1742684108@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/442118.1742684108%40sss.pgh.pa.us>
Precedence: bulk

Kevin Stephenson <kjs714@hotmail.com> writes:
> Christophe and Tom, thank you for your responses, but I'm still a bit co=
nfused. In my original email, the Test 2 case is allowing a ROLLBACK in th=
e EXCEPTION clause without throwing an error. Is it a NOP ROLLBACK being a=
pplied to an aborted subTX, a real full ROLLBACK, or something else? Pleas=
e 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