PostgreSQL subtransactions, savepoints, and exception blocks
TL;DR: similar syntax but very different transaction semantic between Oracle and PostgreSQL procedural blocks
I posted a tricky Quiz on Twitter (unfortunately forgot to mention explicitely that I have a unique constraint on DEMO1.N):
The trick is that I didn’t precise on which database I run that. And I used on purpose a syntax that is valid both for Oracle (with the anonymous block in PL/SQL) and PostgreSQL (with the anonymous block in PL/pgSQL).
A compatible syntax does not mean that the semantic is the same. That’s the common issue with people who think that it is easy to port a database application or build a database-agnostic application. You can speak the same language without understanding the same meaning. The specifications for each implementation goes beyond the apparent standard syntax.
Exception block with Oracle
db<>fiddle — Oracle 19c: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f0f4bf1c0e2e91c210e815d2ac67a688
The PL/SQL block runs within an existing transaction and the exception block has nothing to do with the transaction control. This is only about branching to another code path when an exception occurs.
Then, what was previously inserted is still visible in the transaction, and can be committed or rolled back.
Exception block in Postgres
db<>fiddle — PostgreSQL 12: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=110d82eff25dde2823ff17b4fe9157d9
Here, the PL/pgSQL block runs as an atomic subtransaction. And when an exception is trapped, the whole block is rolled-back before executing the exception block. Actually, the block that has an exception handler is run in a “subtransaction” which is nothing else than setting a savepoint at the begin and rollback to this savepoint when entering the exception block.
This, of course, is documented:
When an error is caught by an
EXCEPTIONclause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back.
In those examples, the exception handler did not raise any error. If I re-raise the error in the exception block, the be behavior is the same between Oracle and PostgreSQL: all changes done by the block (including the exception block) are rolled back.
Re-raise In PostgreSQL:
do $$
begin
insert into DEMO1 (n) values (1);
insert into DEMO1 (n) values (42);
exception when others then
insert into DEMO2 select * from DEMO1;
raise;
end;
$$ language plpgsql;
ERROR: duplicate key value violates unique constraint "demo1_n_key"
DETAIL: Key (n)=(42) already exists.
CONTEXT: SQL statement "insert into DEMO1 (n) values (42)"
PL/pgSQL function inline_code_block line 4 at SQL statementselect * from DEMO2;
n
---
(0 rows)
Re-raise in Oracle:
begin
insert into DEMO1 (n) values (1);
insert into DEMO1 (n) values (42);
exception when others then
insert into DEMO2 select * from DEMO1;
raise;
end;/ORA-00001: unique constraint (DEMO.SYS_C0093607) violated
ORA-06512: at line 6
ORA-06512: at line 3select * from DEMO2;no rows selected.
More info about this behavior in Oracle from Stew Ashton:
Basically, in Oracle, the call to the stored procedure follows statement-level atomicity where an internal savepoint is set before any statement (SQL or PL/SQL) and an unhandled exception (including a re-raise exception) rolls back to it. That’s different in PostgreSQL where no savepoint is set implicitly, and the session has to rollback the whole transaction when an error occurs. The savepoint set before the PL/pgSQL block is only to rollback changes before executing the exception block.
Postgres transaction control and exception blocks
But, then what happens if we commit within the code block? It is then impossible to ensure that “all changes to persistent database state within the block are rolled back” because what is committed (made visible to others) cannot be rolled-back. And that’s the main goal of intermediate commits.
This impossibility is implemented with “ ERROR: cannot commit while a subtransaction is active” in spi.c:
and all this is, of course, documented with a small statement in https://www.postgresql.org/docs/current/plpgsql-transactions.html:
A transaction cannot be ended inside a block with exception handlers.
The specifications for it is also mentioned in the “Transaction control in procedures” hackers thread started by Peter Eisentraut when proposing this feature:
Limitations
As I understand it, this restriction is there to keep the semantics of the subtransaction when an exception block is present. With a savepoint at BEGIN and a rollback to savepoint at EXCEPTION. This semantic specification predates the introduction of transaction control in procedures. However, new requirements to take full advantage of the transaction control in procedures have been raised by Bryn Llewellyn (currently YugaByteDB developer advocate, former Oracle PL/SQL product manager): https://github.com/yugabyte/yugabyte-db/issues/2464
These use-cases are about encapsulating the database calls in stored procedures that, then, expose only the microservice API. For security, performance, and portability this API must be database-agnostic, and then:
- all RDBMS-specific error messages must be trapped and translated to business messages and/or system logs. This must be done in an exception block that also covers the commit-as the commit can fail.
- serialization errors at commit must be re-tried on the server, and that must be done also with an exception block that covers the commit.
Another reason to commit in a procedure is during a large bulk operation where we want intermediate commits. We may want to trap exceptions in this case as well and to retry some operations in case of errors.
If I try to code the commit and the exception, the “cannot commit while a subtransaction is active” error is raised as soon as the “commit” statement is encountered, before even trying to execute it:
create table DEMO(
n integer primary key deferrable initially deferred
);create or replace procedure my_test(n int) as $$
begin
insert into DEMO(n) values(n);
commit;
exception when others then
raise notice '%',sqlerrm;
end;
$$ language plpgsql;CREATE PROCEDUREcall my_test(1);
NOTICE: cannot commit while a subtransaction is active
CALL
If I remove the commit, I can catch the exceptions, but then I must handle the commit error in the client:
create or replace procedure my_test(n int) as $$
begin
insert into DEMO(n) values(n);
--commit;
exception when others then
raise notice '%',sqlerrm;
end;
$$ language plpgsql;CREATE PROCEDUREcall my_test(1);
CALLcall my_test(1);
ERROR: duplicate key value violates unique constraint "demo_pkey"
DETAIL: Key (n)=(1) already exists.
Here the error message does not come from the exception block, but from the end of the command, because I am in autocommit mode. This is more visible from an explicit transaction:
begin transaction;
BEGINcall my_test(1);
CALLcommit;
ERROR: duplicate key value violates unique constraint "demo_pkey"
DETAIL: Key (n)=(1) already exists.
Evolution
I think that the “A transaction cannot be ended inside a block with exception handlers” specification should be adapted to procedures. In my opinion, a commit should be allowed, ending the subtransaction and starting a new one. What was committed will never be rolled back. When an exception is raised, only the changes since the last commit should be rolled back.
Discussion about this should probably go in this hackers thread:
