Different default error handling in Oracle and PostgreSQL

user_4592248 0
Viewed 378

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?

plpgsql plsql postgresql transactions oracle
2 Answers

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.

user_64643080

In Oracle you are using two separate transactions, the first is successful but the second fails. In PostgreSQL, you are explicitly telling it to only use one transaction and handle the statements together.

In Oracle, if you use a PL/SQL anonymous block to group the statements into a single transaction:

BEGIN
  INSERT INTO table1 VALUES (1);
  raise_error();
END;
/

And, equivalently in PostgreSQL:

DO
$$
BEGIN
  INSERT INTO table1 VALUES (1);
  CALL raise_error();
END;
$$ LANGUAGE plpgsql;

Then there will be no rows in the table as the exception from the procedure will rollback the entire transaction.

Or, in Oracle, you could do:

INSERT INTO table1 VALUES (1);

DECLARE
  divide_by_zero EXCEPTION;
  PRAGMA EXCEPTION_INIT( divide_by_zero, -1476 );
BEGIN
  raise_error();
EXCEPTION
  WHEN DIVIDE_BY_ZERO THEN
    ROLLBACK;
END;
/

Which would have the same effect of rolling back both transactions to the last commit.

db<>fiddle Oracle PostgreSQL

user_15092640
Related