Exceptions and Commit in PostgreSQL PL/pgSQL vs. Oracle PL/SQL

In a previous post, I detailed the differences in Exception Handling between Oracle and PostgreSQL.
Now, let's see how the code can be modified to achieve the desired behavior.

To demonstrate the distinct transaction control mechanisms in Oracle Database PL/SQL versus PostgreSQL or YugabyteDB PL/pgSQL, consider the following example:

create table demo (id int primary key);

begin
 insert into demo (id) values(1);
 insert into demo (id) values(1);
exception
  when dup_val_on_index then
   dbms_output.put_line( 'ignoring duplicate');
end;
/
SQL> select * from demo;
   ID
_____
    1

When Oracle enters the exception block, it inserts one row and the code execution continues.

To execute the PL/SQL procedure atomically, you can create a savepoint at the beginning and rollback to this savepoint if an exception occurs:

create table demo (id int primary key);

begin
 savepoint subtransaction;
 insert into demo (id) values(1);
 insert into demo (id) values(1);
exception
  when dup_val_on_index then
   rollback to subtransaction;
   dbms_output.put_line( 'ignoring duplicate');
end;
/
SQL> select * from demo;

no rows selected

In PostgreSQL, PL/pgSQL is ACID. The default behavior for the PL/pgSQL body is atomicity and if the body cannot complete it is rolled back before entering the exceptions block:

create table demo (id int primary key);

do $BODY$
begin
 insert into demo (id) values(1);
 insert into demo (id) values(1);
exception
  when unique_violation then
   raise notice 'ignoring duplicate';
end;
$BODY$;

NOTICE:  00000: ignoring duplicate
LOCATION:  exec_stmt_raise, pl_exec.c:3852
DO

yugabyte=# select * from demo;
 id
----
(0 rows)

In PostgreSQL, the PL/pgSQL body initiates within a subtransaction, establishing an implicit savepoint at the start and rolling back upon entering the exception block.

To mimic Oracle's behavior, you must explicitly commit within the transaction, though some limitations exist:

do $BODY$
begin
 insert into demo (id) values(1);
 commit;
 insert into demo (id) values(1);
exception
  when unique_violation then
   raise notice 'ignoring duplicate';
end;
$BODY$;

ERROR:  2D000: cannot commit while a subtransaction is active
CONTEXT:  PL/pgSQL function inline_code_block line 4 at COMMIT
LOCATION:  SPI_commit, spi.c:250

The procedure failed due to an implicit subtransaction initiated by a savepoint, which prohibits a commit within it. This restriction is intentional to preserve the atomicity of the PL/pgSQL block.

An intermediate commit is possible only when there is no exception block present, thus preventing the start of a subtransaction:

do $BODY$
begin
 insert into demo (id) values(1);
 commit;
 insert into demo (id) values(1);
end;
$BODY$;

ERROR:  23505: duplicate key value violates unique constraint "demo_pkey"
LOCATION:  YBFlushBufferedOperations, ../../src/yb/yql/pggate/pg_perform_future.cc:36

yugabyte=# select * from demo;
 id
----
  1
(1 row)

A drawback of this solution is the inability to code the exception scenario within the PL/pgSQL code. It must be handled from the application.

Additionally, this method is only functional in auto-commit mode. Attempting the same operation within an explicit transaction will result in an error when the COMMIT is executed:

begin transaction;
do $BODY$
begin
 insert into demo (id) values(2);
 commit;
 insert into demo (id) values(2);
end;
$BODY$;

ERROR:  2D000: invalid transaction termination
CONTEXT:  PL/pgSQL function inline_code_block line 4 at COMMIT
LOCATION:  SPI_commit, spi.c:236

The correct approach in PostgreSQL-compatible databases involves utilizing implicit subtransactions through nested exception blocks to design the expected behavior without intermediate commits:

do $BODY$
begin
 insert into demo (id) values(2);
 begin
  insert into demo (id) values(2);
 exception
  when unique_violation then
   raise notice 'ignoring duplicate';
 end;
end;
$BODY$;

NOTICE:  00000: ignoring duplicate
LOCATION:  exec_stmt_raise, pl_exec.c:3852
DO

yugabyte=# select * from demo;
 id
----
  1
  2
(2 rows)

In this case, only the second insert operation was rolled back because the first one, which succeeded, is not within the exception handling block.

There are differences in exception handling and transaction control mechanisms between Oracle Database PL/SQL and PostgreSQL-compatible databases. Oracle offers only statement-level atomicity. A statement is the top-level call, whether it's SQL or PL/SQL, or a recursive SQL statement within the PL/SQL code. On the other hand, PostgreSQL provides additional atomicity for the procedural block, and an exception will roll back its intermediate state.

This difference can create challenges when migrating from Oracle Database to PostgreSQL or PostgreSQL-compatible databases. Identifying the issue requires comprehensive testing that covers all exception scenarios, and it may be necessary to redesign your code to ensure consistent behavior.