Last active
December 3, 2023 14:59
-
-
Save cabecada/2492a2f8f8bc98330f63c30a46a1b363 to your computer and use it in GitHub Desktop.
stored procedure exception handling error
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
https://itecnote.com/tecnote/postgresql-can-a-postgres-commit-exist-in-procedure-that-has-an-exception-block/ | |
https://github.com/postgres/postgres/blob/REL_14_STABLE/src/pl/plpgsql/src/expected/plpgsql_transaction.out | |
There are parts of the procedure where I might want to commit my work so-far so that it won't be rolled back if an exceptions ensues. | |
I want to have an exception handling block at the end of the procedure where I catch the exception | |
and insert the information from the exception into a logging table. | |
I have boiled the problem down to a simple procedure, below, which fails on PostgreSQL 11.2 with | |
2D000 cannot commit while a subtransaction is active | |
PL/pgSQL function x_transaction_try() line 6 at COMMIT | |
drop procedure if exists x_transaction_try; | |
create or replace procedure x_transaction_try() | |
language plpgsql | |
as $$ | |
declare | |
begin | |
raise notice 'A'; | |
-- TODO A: do some insert or update that I want to commit no matter what | |
commit; | |
raise notice 'B'; | |
-- TODO B: do something else that might raise an exception, without rolling | |
-- back the work that we did in "TODO A". | |
exception when others then | |
declare | |
my_ex_state text; | |
my_ex_message text; | |
my_ex_detail text; | |
my_ex_hint text; | |
my_ex_ctx text; | |
begin | |
raise notice 'C'; | |
GET STACKED DIAGNOSTICS | |
my_ex_state = RETURNED_SQLSTATE, | |
my_ex_message = MESSAGE_TEXT, | |
my_ex_detail = PG_EXCEPTION_DETAIL, | |
my_ex_hint = PG_EXCEPTION_HINT, | |
my_ex_ctx = PG_EXCEPTION_CONTEXT | |
; | |
raise notice '% % % % %', my_ex_state, my_ex_message, my_ex_detail, my_ex_hint, my_ex_ctx; | |
-- TODO C: insert this exception information in a logging table and commit | |
end; | |
end; | |
$$; | |
call x_transaction_try(); | |
Why doesn't this stored procedure work? | |
Why is it that we never see the output of raise notice 'B' and instead we go into the exception block? | |
Is it possible to do what I have described above with a Postgres 11 stored procedure? | |
Edit: This is a complete code sample. Paste the above complete code sample (including both the create procedure and call statements) into a sql file and run it in a Postgres 11.2 database to repro. The desired output would be for the function to print A then B, but instead it prints A then C along with the exception information. | |
Also notice that if you comment out all of the exception handling block such that the function does not catch exceptions at all, then the function will output 'A' then 'B' without an exception occurring. This is why I titled the question the way that I did 'Can a Postgres Commit Exist in Procedure that has an Exception Block?' | |
Best Solution | |
The semantics of PL/pgSQL's error handling dictate that: | |
When an error is caught by an EXCEPTION clause ... all changes to persistent database state within the block are rolled back. | |
This is implemented using subtransactions, which are basically the same as savepoints. In other words, when you run the following PL/pgSQL code: | |
BEGIN | |
PERFORM foo(); | |
EXCEPTION WHEN others THEN | |
PERFORM handle_error(); | |
END | |
...what's actually happening is something like this: | |
BEGIN | |
SAVEPOINT a; | |
PERFORM foo(); | |
RELEASE SAVEPOINT a; | |
EXCEPTION WHEN others THEN | |
ROLLBACK TO SAVEPOINT a; | |
PERFORM handle_error(); | |
END | |
A COMMIT within the block would break this completely; your changes would be made permanent, the savepoint would be discarded, and the exception handler would be left with no way to roll back. As a result, commits are not allowed in this context, and trying to execute a COMMIT will result in a "cannot commit while a subtransaction is active" error. | |
That's why you see your procedure jump to the exception handler instead of running the raise notice 'B': when it reaches the commit, it throws an error, and the handler catches it. | |
This is fairly straightforward to work around, though. BEGIN ... END blocks can be nested, and only blocks with EXCEPTION clauses involve setting savepoints, so you can just wrap the commands before and after the commit in their own exception handlers: | |
create or replace procedure x_transaction_try() language plpgsql | |
as $$ | |
declare | |
my_ex_state text; | |
my_ex_message text; | |
my_ex_detail text; | |
my_ex_hint text; | |
my_ex_ctx text; | |
begin | |
begin | |
raise notice 'A'; | |
exception when others then | |
raise notice 'C'; | |
GET STACKED DIAGNOSTICS | |
my_ex_state = RETURNED_SQLSTATE, | |
my_ex_message = MESSAGE_TEXT, | |
my_ex_detail = PG_EXCEPTION_DETAIL, | |
my_ex_hint = PG_EXCEPTION_HINT, | |
my_ex_ctx = PG_EXCEPTION_CONTEXT | |
; | |
raise notice '% % % % %', my_ex_state, my_ex_message, my_ex_detail, my_ex_hint, my_ex_ctx; | |
end; | |
commit; | |
begin | |
raise notice 'B'; | |
exception when others then | |
raise notice 'C'; | |
GET STACKED DIAGNOSTICS | |
my_ex_state = RETURNED_SQLSTATE, | |
my_ex_message = MESSAGE_TEXT, | |
my_ex_detail = PG_EXCEPTION_DETAIL, | |
my_ex_hint = PG_EXCEPTION_HINT, | |
my_ex_ctx = PG_EXCEPTION_CONTEXT | |
; | |
raise notice '% % % % %', my_ex_state, my_ex_message, my_ex_detail, my_ex_hint, my_ex_ctx; | |
end; | |
end; | |
$$; | |
Unfortunately, it does lead to a lot of duplication in the error handlers, but I can't think of a nice way to avoid it. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
create table ins_call( id integer generated always as identity
, proc_name text
);
create or replace procedure sp_insert1()
language sql
as $$
select 1/0;
$$;
create or replace procedure sp_insert2()
language sql
as $$
insert into ins_call(proc_name) values ('sp_insert2()');
$$;
create or replace procedure sp_insert3()
language sql
as $$
select 3/0;
$$;
create or replace procedure master_call_all()
language plpgsql
as $$
declare
begin
begin
call sp_insert1();
exception
when others then
raise notice 'Exception occured calling sp_insert1()';
end ;
commit;
end;
$$;
insert into ins_call(proc_name) values ('Direct SQL');
select * from ins_call;
select * from ins_call;