|
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 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 the clients).
Per my understanding of the docs and some PG source code review:
(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 with the main TX. A similar situation exists for nested procedures that use BEGIN/EXCEPTION, i.e., is there a
coincident subTX inside a subTX?)
In my testing, as shown in the script below, when using structured exception handling in nested stored procedures with an autonomous TX workaround (for error logging), results in error 2D000 (see Test 3). Verbose logging shows 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 ), 2D000 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 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 = 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, sqlerrm)
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 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. Finish it off and start TX 'D'.
ROLLBACK;
INSERT INTO public.error_log (logging_routine_name, 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;
*/
|
