From: Kevin Stephenson <kjs714@hotmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>, Christophe Pettus <xof@thebuild.com>
CC: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination
 2D000
Thread-Topic: Nested Stored Procedures - ERROR: invalid transaction
 termination 2D000
Thread-Index: AQHbmzr5wYrkYV+5b06IaiBcpMu0pbN/rH52
Date: Sat, 22 Mar 2025 21:37:53 +0000
Message-ID:
 <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>
In-Reply-To: <1830668.1742655550@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
msip_labels:
x-ms-exchange-messagesentrepresentingtype: 1
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: IA0PR19MB7217:EE_|PH7PR19MB6875:EE_
x-ms-office365-filtering-correlation-id: c599027f-89cd-4881-a4c2-08dd6989cd30
x-microsoft-antispam:
 BCL:0;ARA:14566002|19110799003|8062599003|15030799003|8060799006|7092599003|15080799006|12050799009|12121999004|461199028|9400799030|10035399004|440099028|4302099013|3412199025|11091999006|102099032|1602099012;
x-microsoft-antispam-message-info:
 =?us-ascii?Q?wj68ZXBWLjFULHw+MKaTPbHvfg8KCAtJkqqaSFRJrDtLtp9UramVnCsTWXQi?=
 =?us-ascii?Q?WjFZT5xCA5LRmvmrWfS0c0Lp90PT3RB37nZ36JgK6fiPUpG6EyOEcH3NDLCy?=
 =?us-ascii?Q?8Mh9hvjshop3/WZEgjyPgAJk/1jb3VaKyfZ9/Be7JeXOfRDmT89XE5ZAJJTj?=
 =?us-ascii?Q?gJcnN9qiMn39pHd03fU/Ftt+/GZdJ36EiNZEB7dGLQLtTYFCxFNc0KODUWlS?=
 =?us-ascii?Q?GS5lugfVzy2zAtcI4Tf0FPGMbOqqxAkBoJNWqmC3Pe8lRs5Cdv6QcjGIjCht?=
 =?us-ascii?Q?rDCob3zz4WY3ayhnsy2GIvcBbOhyeYTopslniNkO/ebWne3rnYkDSTLxlHtQ?=
 =?us-ascii?Q?OfE57dipTwIZigYHii6kn8Uime/tKgMuAxhwoMElmysIjnz0sTfnOWLCJkUJ?=
 =?us-ascii?Q?NRHHvqliOg1f7yl1A0fga3fDzapGUgmREg6kkKPR+utGdLRSLjLTmjOzF0S9?=
 =?us-ascii?Q?EvxQoyLdVtVy8/ZuOICEoC4QmkAR9b+0ETlQv0Mpk6oajHigMezIDrQ1iG/S?=
 =?us-ascii?Q?xBVdvnD4QwsxrnxlO+PBt56st4TW/PNXHrDDI7m9KVuR4PG1PPYXgDi/V6/O?=
 =?us-ascii?Q?y3TBL52nXCtDLgT5PimJfLt/rp2R6u6u17FQLPpiVaQHgp8PjJHZjBqYmIEw?=
 =?us-ascii?Q?tRk0Ex+0lC/93ZXX8oxHyt7DvIWUWiO5vCYLWtjhwYfLaiiI3sJZSXM1rNEd?=
 =?us-ascii?Q?hwnqD/fX9Qvi3e1Kxx+YueDuVWU6R28PtatF3cMPIdc7y/sGJkngn9XW7oVw?=
 =?us-ascii?Q?7HNPbp9879KRjITQIv/oYPkQAJrpjOxFx5x/Q0KS+0jWebqjzQHq+l6D5XOc?=
 =?us-ascii?Q?NlDssc5bA/BMk34wQo1FjLPlwp1ZYvblE4ekfkWfjbPuv36ogkcuN2XB9JgT?=
 =?us-ascii?Q?z6ycNmRHGhXrUDfXiMJogaTT13N4xCL8MI2Ow8xIdbHmDVZO8kNi5qEB12kr?=
 =?us-ascii?Q?dQAFraBcRNx8RJI+9XJMy6meopCoo6cjrr07+j3YdqQ1XtSGcVTg5T7gUfhM?=
 =?us-ascii?Q?Pwrq2EfJnQ33IYaDnWL+0cRXC2ivdspAlllqRLphf5eQmYVvkpdCimiXIJmN?=
 =?us-ascii?Q?+fwAWRHEGFhM9xm3nJi/UZ1X/kbuicNHj9NCcverEtBT4auYpwtJPyBPEMt0?=
 =?us-ascii?Q?St4pvX1ny3yTsGKfLXJtebqcc8FoanlYYuDiIK1ORssv5K5O5zKkF2+Ir2fJ?=
 =?us-ascii?Q?VQ98Ong8IZhDp4RyUHuC8PgEFDTUcUYzK6bGp73TY6fodlEhXi0V26wAHTDe?=
 =?us-ascii?Q?g9sveke/G94+l/nS4dUavPMP2AQu0uixgHO8bTJ6kPyY7wbUOLtoaOuqJOAh?=
 =?us-ascii?Q?n9Mfu1eNSGVsX8YxBapOQfPnuKecUrrQRFO5TrvSPuDHEKOXj2mm13EeAi8/?=
 =?us-ascii?Q?SnYPRwXDKp/pCyj51oPLBk8YxdDHJ3isw26EZPppW4o/ZWYxkB0rHc+A29ft?=
 =?us-ascii?Q?yLCQLYfyIzvoCeZd4mO5U9Giz5NVfjwR?=
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?gEeqtsth/l7SuIaudYkBvXXwul2LVy4W9JrdzISloqoXhT4gUvhqXyd3YrTB?=
 =?us-ascii?Q?LP+K3t2PwqLNzUMIN413+hC+V7d1L1KnkFbRY2hyto54PaJU3zL0Wj6RFEr8?=
 =?us-ascii?Q?KyOGA6RD3WbXp1fCDP99qUX3ISwGLeeyNtwlbufUPke8wADv4so9jrvRpBaU?=
 =?us-ascii?Q?l66KYSn/juykzdiiV/G+ZpPA+KGr3ygra1mhBwCY51HAv7YqUAeKglgNCbp+?=
 =?us-ascii?Q?Ayyy7MWaa9uy3ANGUtZ8S8D/wxV6RdBlrl6WNel7ZjFGO7jMHYmUFN6+JGs7?=
 =?us-ascii?Q?BPcVwNLNsSbEbRh9umy7zgWIR1QXgMH+ncTU15/USGnzJk2SkG9H95lKfmkQ?=
 =?us-ascii?Q?aJCMlmZqcUrHr+O1IgMDa7zxHzRvJ5eKBqBcw/86wHJ5ZM4nuU7T9Dx+efYP?=
 =?us-ascii?Q?50lw+MRSP32csudcJ1AAgDiM9LF4pAO9U+aUUKI2eDsDeDODRNEylqZmmB1d?=
 =?us-ascii?Q?ZZpFt/gZJquUI38vbapaL2PutL+XdJy9G3dp9+gvKyLubDmYdZekU+gT/FnB?=
 =?us-ascii?Q?YscU+pjf2ejDT1awJ8Xr6HRE2A+LPsrunnUBNZJILtU3Irzym6dYix+3xWtP?=
 =?us-ascii?Q?Gqnva2gmaJyWMKsPHQd1sR2T5TKjFZurbwtso2dZmIboGg9awQnQVkJeL1qL?=
 =?us-ascii?Q?vy0xrSTh9BdAg9Dcr1+hdRJ7OebHCd8d9/UvZr0ZuhHr6lsKjCWS/d742tla?=
 =?us-ascii?Q?HCXkiHaEuMt89mWCJXkrpg7DKtVq01O10u637/I9uiH0dGtVEwY8rkgyIeZ6?=
 =?us-ascii?Q?GRoxm6xsug56FNiUmxGyuqd0odz7oljrwnTeTdaQEjib9x3Hnlh1WRnJ/FTP?=
 =?us-ascii?Q?R6SJhVEl1Qd4lQBN4cqtdh5g0GIUTkjyW9N26b5R9qSouF0D7PkIoBDtR7GA?=
 =?us-ascii?Q?yDxP8FMAfDCHZh9O4Gb+aGVRy4Ozxgj30UhTDSGKOQcxcJTGo0ms0EZTRVQL?=
 =?us-ascii?Q?LFLGOgGHWvbaMYBUjwKf/acvEcY/3kpa1W6lxZRLD4eo+6Ts7yeUeXOfhCa9?=
 =?us-ascii?Q?mDBlTWvRdCswYMXpFq7tcQUp7LTual0EpIcLOPirdPV6wFVyYjfr3+2kl09S?=
 =?us-ascii?Q?oVAy104qkit7WxIzGIJP2kmjSoBvAfvvouQ5CFRM227OaWYggIhyUGAXpcSm?=
 =?us-ascii?Q?Mb6UiV2CgujarUdeK8008cp6ge587hgfL/eEiw7ytMeAYU8sCi18ra2IlX5x?=
 =?us-ascii?Q?VZyUeU0peJ02LMo+PPYPOjnLrAIzwwyh+uqS4Q=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2IA0PR19MB7217namp_"
MIME-Version: 1.0
X-OriginatorOrg: sct-15-20-7741-18-msonline-outlook-c907d.templateTenant
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: IA0PR19MB7217.namprd19.prod.outlook.com
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000
X-MS-Exchange-CrossTenant-Network-Message-Id: c599027f-89cd-4881-a4c2-08dd6989cd30
X-MS-Exchange-CrossTenant-originalarrivaltime: 22 Mar 2025 21:37:53.0231
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: PH7PR19MB6875
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2%40IA0PR19MB7217.namprd19.prod.outlook.com>
Precedence: bulk

--_000_IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2IA0PR19MB7217namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Christophe and Tom, thank you for your responses, but I'm still a bit confu=
sed. In my original email, the Test 2 case is allowing a ROLLBACK in the EX=
CEPTION clause without throwing an error. Is it a NOP ROLLBACK being applie=
d to an aborted subTX, a real full ROLLBACK, or something else? Please advi=
se.

Thanks,
Kevin Stephenson
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Saturday, March 22, 2025 7:59 AM
To: Christophe Pettus <xof@thebuild.com>
Cc: Kevin Stephenson <kjs714@hotmail.com>; pgsql-general@postgresql.org <pg=
sql-general@postgresql.org>
Subject: Re: Nested Stored Procedures - ERROR: invalid transaction terminat=
ion 2D000

Christophe Pettus <xof@thebuild.com> writes:
> A procedure cannot issue top-level transaction control statements from wi=
thin 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=3Dhttps%3A%2F%2Fwww.=
postgresql.org%2Fdocs%2Fcurrent%2Fdblink.html&data=3D05%7C02%7C%7Ce846300d6=
b9c402760ec08dd69521aad%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638782=
523529471489%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMD=
AwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=
=3D2Rn9iT1VcDJgCXesww3AcwD16UIWE3HsEgniD0Byodk%3D&reserved=3D0<https://www.=
postgresql.org/docs/current/dblink.html>

--_000_IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2IA0PR19MB7217namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Christophe and Tom, thank you for your responses, but I'm still a bit confu=
sed. In my original email, the Test 2 case is allowing a ROLLBACK in the EX=
CEPTION clause without throwing an error. Is it a NOP ROLLBACK being applie=
d to an aborted subTX, a real full
 ROLLBACK, or something else? Please advise.</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Thanks,</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Kevin Stephenson</div>
<div id=3D"appendonsend"></div>
<hr style=3D"display:inline-block;width:98%" tabindex=3D"-1">
<div id=3D"divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" st=
yle=3D"font-size:11pt" color=3D"#000000"><b>From:</b> Tom Lane &lt;tgl@sss.=
pgh.pa.us&gt;<br>
<b>Sent:</b> Saturday, March 22, 2025 7:59 AM<br>
<b>To:</b> Christophe Pettus &lt;xof@thebuild.com&gt;<br>
<b>Cc:</b> Kevin Stephenson &lt;kjs714@hotmail.com&gt;; pgsql-general@postg=
resql.org &lt;pgsql-general@postgresql.org&gt;<br>
<b>Subject:</b> Re: Nested Stored Procedures - ERROR: invalid transaction t=
ermination 2D000</font>
<div>&nbsp;</div>
</div>
<div class=3D"BodyFragment"><font size=3D"2"><span style=3D"font-size:11pt;=
">
<div class=3D"PlainText">Christophe Pettus &lt;xof@thebuild.com&gt; writes:=
<br>
&gt; A procedure cannot issue top-level transaction control statements from=
 within an exception block, and attempting to do so raises the error you sa=
w.&nbsp; This includes procedures that are called from within an exception =
block.<br>
<br>
Yeah.&nbsp; Postgres doesn't have autonomous transactions (not yet anyway),=
<br>
and you can't fake them like that.<br>
<br>
A way that does work, I believe, is to set up a second session with<br>
dblink[1] and use that to issue the autonomous transaction.&nbsp; Ugly<br>
and inefficient for sure, but if you've gotta have it...<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; regards, to=
m lane<br>
<br>
[1] <a href=3D"https://www.postgresql.org/docs/current/dblink.html">https:/=
/na01.safelinks.protection.outlook.com/?url=3Dhttps%3A%2F%2Fwww.postgresql.=
org%2Fdocs%2Fcurrent%2Fdblink.html&amp;data=3D05%7C02%7C%7Ce846300d6b9c4027=
60ec08dd69521aad%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C6387825235294=
71489%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsI=
lAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&amp;sdata=3D2R=
n9iT1VcDJgCXesww3AcwD16UIWE3HsEgniD0Byodk%3D&amp;reserved=3D0</a><br>
</div>
</span></font></div>
</body>
</html>

--_000_IA0PR19MB7217A4E99BAAB1FDF576AC738FDA2IA0PR19MB7217namp_--