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 1tvuN0-0091me-UD for pgsql-general@arkaria.postgresql.org; Sat, 22 Mar 2025 08:38:19 +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 1tvuMy-008sRg-Fs for pgsql-general@arkaria.postgresql.org; Sat, 22 Mar 2025 08:38:16 +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 1tvuMx-008sRY-UP for pgsql-general@lists.postgresql.org; Sat, 22 Mar 2025 08:38:16 +0000 Received: from mail-bn7nam10olkn2088.outbound.protection.outlook.com ([40.92.40.88] helo=NAM10-BN7-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tvuMv-000Sxm-2C for pgsql-general@postgresql.org; Sat, 22 Mar 2025 08:38:15 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=aYDCt+k/FKZTTDgyUC1srHD4bFXv8+IFgCSYg/CA/meJlU17JjOCqNylB1ARHlW6AzH7P56WmZnUQ80OUlxvY/+4CfyVWJucETxLdHSgHxKcbzEeC2eRR5n55qs5ymXi1f6HEvZHC4UTJslo9qfgFOwLxdfLkn98pcgf4RP6zgzWUqbaaR2Db8QcM+4ReIB5BGPsgQqIQjMH3IfvAGHnqqx6Qx7HTd6jR++namqTnp5Ri+S5gHiuxEdWT/TRLfbIcMu2kLbg/mxaQX+kJjxx1O1zUJEB4eTVGJOOkt5SLJ11nleuwTgMa8AWzDY78I6RVBU3VdmIeofNr8I7gGmXCQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=LLmKgddlgyMD9I3mnsJaZ6/af0GRv2k7n/XAy7knF24=; b=MuZgbafdaRJYM8TP/CFgN2mWrUpNn9yJkDkL1mk+ffgo/Wx7MlGBSwBIQLehy9ud/ry6D6cXf7MgtvsyI7TDK+mYfqlO/N3Yt8FeHRs8AUBM20bBKu5hiGDjNQc2seZBpnwp8uYFXUcqfYlp3iewzVrXplv2z73jGCbNngycVFrl8sc9rsfzR5kFYKWYFklTpU6+SpI1GCfj4imNv2BhGe1ZGYRuHNmnyaBEy6m9sbo1fENoA9ULms0nVxRUYEg2KSJDSd0v3SdGKNSZgtn4NrKiviFyXZ8kb2JsuUdOwOnAe91KgdyrWd3DegP4KeEQtZNp5t1TK+kuSzwtx1vxCA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=LLmKgddlgyMD9I3mnsJaZ6/af0GRv2k7n/XAy7knF24=; b=eeYg+CXuidus9IHsDMlZlemgxATr7t4W44trMKRr7HU7TIV7P5CcmZPFOTMdMgGCmcagUVBIKnHSzTlnWMWlVftFkuDEigBVTsVwWqlPskPoAIHHPqfrXFfz9AZEYOk5lxYy+ksllV6WwpwoknihGfxKyXtNESrYtST1kFtwlEyIZtSVZcs9DHedfCUX2n25aiK155BWWwXs5rGNmRZChFdwdtu4t0QuUWBPH50z5tusianvKcCtjF8PW3b+br1sYCCB832e/4EMYy9QrfwJp96HTe885IgBdDgn21F2VNDW05C4hsJGKIN68JWjce8CRMZMWBvv6uLBPqTvSq+ybw== Received: from IA0PR19MB7217.namprd19.prod.outlook.com (2603:10b6:208:43f::21) by MN0PR19MB6168.namprd19.prod.outlook.com (2603:10b6:208:3cc::22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8534.36; Sat, 22 Mar 2025 08:38:09 +0000 Received: from IA0PR19MB7217.namprd19.prod.outlook.com ([fe80::c8d1:2003:98c6:348c]) by IA0PR19MB7217.namprd19.prod.outlook.com ([fe80::c8d1:2003:98c6:348c%5]) with mapi id 15.20.8534.036; Sat, 22 Mar 2025 08:38:09 +0000 From: Kevin Stephenson To: "pgsql-general@postgresql.org" Subject: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 Thread-Topic: Nested Stored Procedures - ERROR: invalid transaction termination 2D000 Thread-Index: AQHbmwBJrrmGcjeUnU+q7x5i1U6fFA== Date: Sat, 22 Mar 2025 08:38:09 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-ms-publictraffictype: Email x-ms-traffictypediagnostic: IA0PR19MB7217:EE_|MN0PR19MB6168:EE_ x-ms-office365-filtering-correlation-id: 23bac11a-da28-4134-e3af-08dd691ce005 x-microsoft-antispam: BCL:0;ARA:14566002|8062599003|12121999004|461199028|8060799006|15030799003|19110799003|15080799006|7092599003|440099028|3412199025|41001999003|11091999006|102099032; x-microsoft-antispam-message-info: =?iso-8859-1?Q?xBKPyHovrk+OZ46mtd8bOeHU5vPAhdEN2YX9tRMofi1vJdCxDJhSaM65O3?= =?iso-8859-1?Q?Xb+IqjwylCwztAlVeUrGTHj4c6Dn42HtNxDNnxsr0+dh9cjTupZZqfp4dM?= =?iso-8859-1?Q?It4eYqfKaueb2OmT3R+2fhMnEuyffO52IgtfxBxfLmkL+qawhiNncZ+qvt?= =?iso-8859-1?Q?eC1iynVlpYxt/pI61wCWGv4FjVZD+EVQ2P9P7CAgvOPLfw6XSBkZ9eTzRu?= =?iso-8859-1?Q?2Oe7jp/z8LDxlPsMKQ+wB2Oxz4eaS+BJim3eK4wxKNkvGQz3AnYBBHmmpl?= =?iso-8859-1?Q?R5v7kK5seas7Ih1jPEoX0MlaYP8jmnVEriQMnKrGHCtDKwyh36FvBX//HC?= =?iso-8859-1?Q?AGAz+M+tmbmALJ5Geb0YWN8md/ik7fdjaBB90oEmzaQDWq+yHJvToYftUy?= =?iso-8859-1?Q?1zbpamw1o7r9+11GhYZE2KPNLorxcKQbHfeyGQajrGhOnz2kZAvpLTx1hj?= =?iso-8859-1?Q?JfV8IIRx2NPKoYlAuU6MykKE9yu6uqHE7FbnWIi70r1LEFeP2euW3ZWWxQ?= =?iso-8859-1?Q?w/hrxF122IkWGgwdIi3TcdjbreE1SynleDca+t+euDMf9DhQoj0mOlTI3T?= =?iso-8859-1?Q?VpfkEcthdHlKbzHhyPXwgIcs+KSYzbmGiasVFZOIgLAd9YC4XCO/KWeu5v?= =?iso-8859-1?Q?1W8kKm+Y5Rj79R3n7DvYkJq6CNihURTUl0hpO4Bh3/hkr8HvQuOauBThM9?= =?iso-8859-1?Q?Rtp4s/sJYw4EnZnn8oJ+W6MnbVKzzwSb7Os3fqt5VdqpHiJVDYumBSLNK/?= =?iso-8859-1?Q?e66IeTVbaJ8UQF3f041rWJXy+9uiM14Rq59ggqfNGWS2XCx9Wy5R1YYhCJ?= =?iso-8859-1?Q?Frc2V2FjbCZFGuFyOg9uLLvaEz+hfErTrTbIvUWHqxf71+YrrGBMl8eU3l?= =?iso-8859-1?Q?6U24e9KVK10+P9XdFoOsog6RTLS+crqvkkJSQewdlPEtcgSsQT7MxG38yA?= =?iso-8859-1?Q?XraOa6MswbkYI5aZgt2Y9iZ3o28Jq1awZersu7tIqlcJByZP2x6HtrSv6U?= =?iso-8859-1?Q?U+Sg5viGkK0a+YaZrMd03UFseIKfiy63CeGclI/bVGHpBVsLPEASwlPVp8?= =?iso-8859-1?Q?4X5tXpdvVfG/ZsXR6R/vWX2ezqLJs7DIX+dRbfULOf1ZtuVivCQKAFF6Wf?= =?iso-8859-1?Q?/J8SiJXrZzygUwPu8qDuAOBDX6UtMw6WmAWj3oLwBPJleKCfUrXJWLvM2/?= =?iso-8859-1?Q?htFWmayvpu/q69Ya2KdI6KpI/LooGTZcOZiJP/PO8ks/8mFbYpI5j9O/ze?= =?iso-8859-1?Q?rCqgR4uc3tOX1ay49hKfJAJ1c5Q1dB+Rjb42aP2ZU=3D?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?DPUi+qKoaioQ63SDhxNc4UZeVlenw8aEJkQjNF8rQiz1T2MnSAiqIoFqUu?= =?iso-8859-1?Q?us26OVK5dEDRk2u+79h6RQCjLf/kkj5wixL3ScVR4uo9BeUfrCWl2zp/vs?= =?iso-8859-1?Q?BkqJkwalYmrkVPRts11mrnfj234lBC1pRcU8h4eduHJQgM1ZkIyrNhOg1D?= =?iso-8859-1?Q?Ap+bIgA1U0pH4ebkKmL6X1Hn4xdlHxaF9YTddNDJVnEWw/ekqycjllsh1z?= =?iso-8859-1?Q?LuegdtMiBYR5DcIaP5k8imxx4iHwkrKtWqhT4J3CJ0Yy9NezhykzuxhiTY?= =?iso-8859-1?Q?Rra2mPUddGBs/suYOh6Hl8dkVvKSRodJfQIZN4OG8rWhhRIMtZD61yM6ST?= =?iso-8859-1?Q?AYr35SxfXXOkMH/swyHUFZ5O4XJLxGhk7W0Elsr5gMFpinGAQUzjcIYZVS?= =?iso-8859-1?Q?3e73/RhufAr7q1xT+Xoc6LocVfpQGvkeJ6I/2GWlAizmnjQsZb7Ctsje9V?= =?iso-8859-1?Q?I5Z5ExY8TCZKH/CrWrDj9PNrS/IQPX7Pxzb1tW7W45tL9a8wthCBaZmqbo?= =?iso-8859-1?Q?qpUSkw4u8SL76w8+6CgQ1WCTbOwZkCe42beLtaFDLGaPpArAiKsGRmuDoM?= =?iso-8859-1?Q?o8MDJcfjUvN64EN3VUYG8z2SJcrUbOwI1AjItI6qNryqXlhgdYr2Sskm9t?= =?iso-8859-1?Q?6BsVdJNEM1Y/ft9FqMPoYhez92SQwy4stvMERu/ltwY4pLZszRKhR7QCQu?= =?iso-8859-1?Q?EsxKux5x3q7FPIjVfIT1IQ8kEdpcPlJafv2kKvgxlkVdTOMluq/+xvQK7W?= =?iso-8859-1?Q?qqZ6o8WTBVLyASCQKDibePHSNykk+Fw/cvvc56GY+qD0wLSgJF1J3eFiD5?= =?iso-8859-1?Q?o4vYmXOFltNgIGhLedI8ECYwXVhj0LK0vVFKFkoHQdnx+sfvOFNR/k5J1h?= =?iso-8859-1?Q?GxY3HcYvxzude4bMyeHDFMlGOv0dsrkncUD8BNqjb4VbyFrxp+IJENs/h+?= =?iso-8859-1?Q?ujpU/s+Wghn8jrdMhaBdL9BisAOre9+4TF8N6puU2m9xZtw2GceWocKJ+N?= =?iso-8859-1?Q?s9gGQw882LHEuJ7Ej/tJZwOtp1dH07/zgx4rloGITaYM+wwiYnxuWF7K8F?= =?iso-8859-1?Q?Rh5ELu7Qmoo4CaqJeKEiL4fWGg5k326XLSss3JL4q1bFE96/QBu1pKtNto?= =?iso-8859-1?Q?h6Y+Ndb85a+L4O/R09WZLjb40Zqr3FVY+C+y5yZgEQJhQUepIDSpmlLPgy?= =?iso-8859-1?Q?Rkq1AgKmwRT6ARaegBCzTuHnCLH/cZ0jqoXMEstYPwmXexfFrzA3nOdm+D?= =?iso-8859-1?Q?kAJ73U72g7b7L//dkuhg=3D=3D?= Content-Type: multipart/alternative; boundary="_000_IA0PR19MB721723603709836EE5D0B17E8FDA2IA0PR19MB7217namp_" 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: 23bac11a-da28-4134-e3af-08dd691ce005 X-MS-Exchange-CrossTenant-originalarrivaltime: 22 Mar 2025 08:38:09.4695 (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: MN0PR19MB6168 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_IA0PR19MB721723603709836EE5D0B17E8FDA2IA0PR19MB7217namp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I'm assessing the feasibility of implementing a full featured "DB as API" c= oncept in PostgreSQL (PG) and have run across an apparent inconsistency in = the transaction (TX) handling behavior with nested stored procedures. This = apparent inconsistency is present in both 16.4 and 17.4 running on Linux. I= 'm using pgAdmin and other clients with the default autocommit behavior (i.= e. no AUTOCOMMIT OFF magic START TRANSACTION; commands are being sent by th= e clients). Per my understanding of the docs and some PG source code review: * When a top-level stored procedure is called it implicitly creates a TX if t= here is no current TX. * When a nested stored procedure is called it implicitly creates a subTX for = that invocation. * When a BEGIN/EXCEPTION block is used it implicitly creates a subTX for that= block. (It is not clear if a top-level procedure that uses BEGIN/EXCEPTION in the = outermost block bothers with a subTX as it would be logically coincident wi= th the main TX. A similar situation exists for nested procedures that use B= EGIN/EXCEPTION, i.e., is there a coincident subTX inside a subTX?) In my testing, as shown in the script below, when using structured exceptio= n handling in nested stored procedures with an autonomous TX workaround (fo= r error logging), results in error 2D000 (see Test 3). Verbose logging show= s it to be caused by function _SPI_rollback() at line 400 (16.4) or 399 (17= .4) in spi.c. What seems inconsistent is that if the outer procedure does n= ot use an EXCEPTION block (see Test 2 ), 2D000 is not thrown and the autono= mous TX workaround works as desired. Please advise if this is expected behavior. Much thanks, Kevin Stephenson -- WARNING: Script contains DROP statements. -- Greatly simplified schema for demonstration. DROP TABLE IF EXISTS public.error_log; CREATE TABLE public.error_log ( logging_routine_name text NULL, sqlstate text NULL, sqlerrm text NULL ); DROP TABLE IF EXISTS public.dummy; CREATE TABLE public.dummy ( data text NULL ); CREATE OR REPLACE PROCEDURE public.inner_proc() LANGUAGE plpgsql AS $$ DECLARE dummy_var int; BEGIN -- Assuming subTX implicitly starts under (main) TX 'A' INSERT INTO public.dummy (data) VALUES ('inner_proc'); dummy_var =3D 1/0; EXCEPTION -- Assuming only subTX implicitly rolled back WHEN OTHERS THEN -- Autonomous TX workaround. ROLLBACK; -- rollback TX 'A' and start new TX 'B' INSERT INTO public.error_log (logging_routine_name, sqlstate, sqler= rm) VALUES ('inner_proc', SQLSTATE, SQLERRM); -- commit TX 'B' and start new TX 'C' COMMIT; -- Autonomous TX workaround finished. -- Rethrow for caller to handle. RAISE; END;$$; CREATE OR REPLACE PROCEDURE public.outer_proc_simple() LANGUAGE plpgsql AS $$ BEGIN -- TX 'A' starts here -- Simple example with no exception handling in outer proc. INSERT INTO public.dummy (data) VALUES ('outer_proc_simple'); CALL public.inner_proc(); -- TX 'C' in aborted state with uncaught exception bubbling up to calle= r. END;$$; CREATE OR REPLACE PROCEDURE public.outer_proc_complex() LANGUAGE plpgsql AS $$ BEGIN -- TX 'A' starts here -- Complex example that allows additional error logging. INSERT INTO public.dummy (data) VALUES ('outer_proc_complex'); CALL public.inner_proc(); EXCEPTION WHEN OTHERS THEN -- TX 'C' should already be in aborted state. Finish it off and sta= rt TX 'D'. ROLLBACK; INSERT INTO public.error_log (logging_routine_name, sqlstate, sqler= rm) VALUES ('outer_proc', SQLSTATE, SQLERRM); -- We want to rethrow again so commit TX 'D'. COMMIT; RAISE; -- app layer can handle as appropriate END;$$; -- Test 1 (Works as expected.) CALL public.inner_proc(); /* ERROR: division by zero CONTEXT: ... (truncated for brevity) */ SELECT * FROM public.dummy; -- empty result set SELECT * FROM public.error_log; -- inner_proc, 22012, division by zero -- Test 2 (Works as expected.) TRUNCATE TABLE public.dummy; TRUNCATE TABLE public.error_log; -- Note: Do not run TRUNCATEs and CALL in a single batch. -- Creates an outer TX that would not be done in real use. CALL public.outer_proc_simple(); /* ERROR: division by zero CONTEXT: ... (truncated for brevity) */ SELECT * FROM public.dummy; -- empty result set SELECT * FROM public.error_log; -- inner_proc, 22012, division by zero -- Test 3 (Fails?) TRUNCATE TABLE public.dummy; TRUNCATE TABLE public.error_log; -- CALL public.outer_proc_complex(); /* ERROR: invalid transaction termination CONTEXT: PL/pgSQL function inner_proc() line 14 at ROLLBACK SQL statement "CALL public.inner_proc()" PL/pgSQL function outer_proc_complex() line 6 at CALL SQL state: 2D000 */ SELECT * FROM public.dummy; -- empty result set SELECT * FROM public.error_log; -- outer_proc, 2D000, invalid transaction termination -- Cleanup. /* DROP PROCEDURE IF EXISTS public.outer_proc_complex; DROP PROCEDURE IF EXISTS public.outer_proc_simple; DROP PROCEDURE IF EXISTS public.inner_proc; DROP TABLE IF EXISTS public.error_log; DROP TABLE IF EXISTS public.dummy; */ --_000_IA0PR19MB721723603709836EE5D0B17E8FDA2IA0PR19MB7217namp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi all,

I'm assessing the feasibility of implementing a full featured "DB= as API" concept in PostgreSQL (PG) and have run across an apparent in= consistency in the transaction (TX) handling behavior with nested stored pr= ocedures. This apparent inconsistency is present in both 16.4 and 17.4 running on Linux. I'm using pgAdmin and other client= s with the default autocommit behavior (i.e. no AUTOCOMMIT OFF magic START = TRANSACTION; commands are being sent by the clients).

Per my understanding of the docs and some PG source code review:

  • When a top-level stored procedure is called i= t implicitly creates a TX if there is no current TX.
  • When a nested stored procedure is called it i= mplicitly creates a subTX for that invocation.
  • When a BEGIN/EXCEPTION block is used it impli= citly creates a subTX for that block.

(It is not clear if a top-level procedure that uses BEGIN/EXCEPTION in the = outermost block bothers with a subTX as it would be logically coincident wi= th the main TX. A similar situation exists for nested procedures that use B= EGIN/EXCEPTION, i.e., is there a coincident subTX inside a subTX?)

In my testing, as shown in the script below, when using structured exceptio= n handling in nested stored procedures with an autonomous TX workaround (fo= r error logging), results in error 2D000 (see Test 3). Verbose logging show= s it to be caused by function _SPI_rollback() at line 400 (16.4) or 399 (17.4) in spi.c. What seems inconsistent is that= if the outer procedure does not use an EXCEPTION block (see Test 2 ), 2D00= 0 is not thrown and the autonomous TX workaround works as desired.

Please advise if this is expected behavior.

Much thanks,
Kevin Stephenson

-- WARNING: Script contains DROP statements.
-- Greatly simplified schema for demonstration.
DROP TABLE IF EXISTS public.error_log;
CREATE TABLE public.error_log (
    logging_routine_name    text NULL,  
    sqlstate               &nb= sp;text NULL,
    sqlerrm               &nbs= p; text NULL
);

DROP TABLE IF EXISTS public.dummy;
CREATE TABLE public.dummy (
    data                 =    text NULL
);

CREATE OR REPLACE PROCEDURE public.inner_proc()
LANGUAGE plpgsql AS $$
DECLARE
    dummy_var int;

BEGIN
    -- Assuming subTX implicitly starts under (main) TX 'A'
    INSERT INTO public.dummy (data) VALUES ('inner_proc');
    dummy_var =3D 1/0;

EXCEPTION
    -- Assuming only subTX implicitly rolled back
    WHEN OTHERS THEN
        -- Autonomous TX workaround.
        ROLLBACK; -- rollback TX 'A' and start new TX '= B'

        INSERT INTO public.error_log (logging_routine_n= ame, sqlstate, sqlerrm)
            VALUES ('inner_proc', SQLSTATE, S= QLERRM);

        -- commit TX 'B' and start new TX 'C'
        COMMIT;
        -- Autonomous TX workaround finished.

        -- Rethrow for caller to handle.
        RAISE;

END;$$;

CREATE OR REPLACE PROCEDURE public.outer_proc_simple()
LANGUAGE plpgsql AS $$
BEGIN
    -- TX 'A' starts here
    -- Simple example with no exception handling in outer proc.
    INSERT INTO public.dummy (data) VALUES ('outer_proc_simple');=
    CALL public.inner_proc();
    -- TX 'C' in aborted state with uncaught exception bubbling u= p to caller.
END;$$;

CREATE OR REPLACE PROCEDURE public.outer_proc_complex()
LANGUAGE plpgsql AS $$
BEGIN
    -- TX 'A' starts here
    -- Complex example that allows additional error logging.
    INSERT INTO public.dummy (data) VALUES ('outer_proc_complex')= ;
    CALL public.inner_proc();

EXCEPTION
    WHEN OTHERS THEN
        -- TX 'C' should already be in aborted state. F= inish it off and start TX 'D'.
        ROLLBACK;

        INSERT INTO public.error_log (logging_routine_n= ame, sqlstate, sqlerrm)
        VALUES ('outer_proc', SQLSTATE, SQLERRM);  = ;

        -- We want to rethrow again so commit TX 'D'.
        COMMIT;
        RAISE; -- app layer can handle as appropriate
END;$$;

-- Test 1 (Works as expected.)
CALL public.inner_proc();
/*
ERROR:  division by zero
CONTEXT:  ... (truncated for brevity)
*/
SELECT * FROM public.dummy;
-- empty result set
SELECT * FROM public.error_log;
-- inner_proc, 22012, division by zero

-- Test 2 (Works as expected.)
TRUNCATE TABLE public.dummy;
TRUNCATE TABLE public.error_log;
-- Note: Do not run TRUNCATEs and CALL in a single batch.
-- Creates an outer TX that would not be done in real use.
CALL public.outer_proc_simple();
/*
ERROR:  division by zero
CONTEXT:  ... (truncated for brevity)
*/
SELECT * FROM public.dummy;
-- empty result set
SELECT * FROM public.error_log;
-- inner_proc, 22012, division by zero

-- Test 3 (Fails?)
TRUNCATE TABLE public.dummy;
TRUNCATE TABLE public.error_log;
--
CALL public.outer_proc_complex();
/*
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function inner_proc() line 14 at ROLLBACK
SQL statement "CALL public.inner_proc()"
PL/pgSQL function outer_proc_complex() line 6 at CALL

SQL state: 2D000
*/
SELECT * FROM public.dummy;
-- empty result set
SELECT * FROM public.error_log;
-- outer_proc, 2D000, invalid transaction termination

-- Cleanup.
/*
DROP PROCEDURE IF EXISTS public.outer_proc_complex;
DROP PROCEDURE IF EXISTS public.outer_proc_simple;
DROP PROCEDURE IF EXISTS public.inner_proc;
DROP TABLE IF EXISTS public.error_log;
DROP TABLE IF EXISTS public.dummy;
*/
--_000_IA0PR19MB721723603709836EE5D0B17E8FDA2IA0PR19MB7217namp_--