Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tw9sq-00BHsp-Ac for pgsql-general@arkaria.postgresql.org; Sun, 23 Mar 2025 01:12:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tw9so-006KoR-Lp for pgsql-general@arkaria.postgresql.org; Sun, 23 Mar 2025 01:12:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tw9so-006KoJ-Az for pgsql-general@lists.postgresql.org; Sun, 23 Mar 2025 01:12:10 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tw9sm-000ap3-0Y for pgsql-general@postgresql.org; Sun, 23 Mar 2025 01:12:09 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 52N1C5gt462136; Sat, 22 Mar 2025 21:12:05 -0400 From: Tom Lane To: Christophe Pettus cc: Kevin Stephenson , "pgsql-general@postgresql.org" Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 In-reply-to: References: <12B83964-7B3B-4345-9952-F8FE61727CB5@thebuild.com> <1830668.1742655550@sss.pgh.pa.us> Comments: In-reply-to Christophe Pettus message dated "Sat, 22 Mar 2025 22:18:07 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <462134.1742692325.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Sat, 22 Mar 2025 21:12:05 -0400 Message-ID: <462135.1742692325@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Christophe Pettus writes: > That's an interesting question. It appears to be a no-op, although a qu= ick 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=3D# 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