Different default error handling in Oracle and PostgreSQL

Question

I compare the default behavior of Oracle and PostgreSQL after encountering an error in a PL/SQL (PL/pgSQL) code. For this purpose, I wrote an analogous Oracle and PostgreSQL code shown below.

Oracle code (db<>fiddle):

CREATE TABLE table1 (col1 int);

CREATE PROCEDURE raise_error AS
BEGIN
  INSERT INTO table1 VALUES (1/0);
END;
/

INSERT INTO table1 VALUES (1);
CALL raise_error();
COMMIT;
SELECT * FROM table1;

PostgreSQL code (db<>fiddle):

CREATE TABLE table1 (col1 int);

CREATE PROCEDURE raise_error() AS $$
BEGIN
  INSERT INTO table1 VALUES (1/0);
END;
$$ LANGUAGE plpgsql;

BEGIN TRANSACTION; -- disable auto-commit

INSERT INTO table1 VALUES (1);
CALL raise_error();
COMMIT;
SELECT * FROM table1;

Note: In PostgreSQL I additionally run the BEGIN TRANSACTION statement to disable auto-commit, because Oracle doesn't have auto-commit, and I want both codes to be analogous.

The result of the SELECT * FROM table1 query is one row in Oracle, and no rows in PostgreSQL.

As you can see, the analogous code in Oracle and PostgreSQL gives different results. What is the reason of this difference in the default error handling?

Answer 1

Oracle and PostgreSQL indeed behave differently here.

Oracle has something that I would call “statement-level rollback”: if a statement running inside a transaction causes an error, only the effects of that statement are rolled back, and the transaction continues.

In PostgreSQL, any error inside a transaction aborts the whole transaction, so you can only roll back the transaction, and it has no effects at all. This is more in the spirit of “all or nothing”, but as far as I can see, the SQL standard is not specific about this, so both can behaviors can be argued.

You can, however, use standard conforming savepoints in PostgreSQL to “recover” from an error in a transaction:

START TRANSACTION;

INSERT INTO table1 VALUES (1);

/* set a savepoint we can revert to */
SAVEPOINT x;

CALL raise_error();

ROLLBACK TO SAVEPOINT x;

/* now the INSERT can be committed */
COMMIT;

But be warned that you don't use too many savepoints (not more than 64) per transaction, else performance may suffer.