Learning PostgreSql: XACT_ABORT is Always ON

PostgreSql has simple and consistent error handling, which can be roughly explained in Sql Server terms as follows: XACT_ABORT is Always ON. In other words, error handling in PostgreSql has substantially less features, but it does have all the features which we actually use in the project being migrated.

Simplicity of error handling in PostgreSql is very good news - we need to learn much less before we are productive, and there are less chances to make a mistake. Let us consider some examples.

On errors, transactions roll back.

This behavior is similar to what Sql Server does when XACT_ABORT is set ON.

Let us add a test table, and add one row of test data:

CREATE TABLE test(ID INT NOT NULL, col2 INT NOT NULL, col3 FLOAT);INSERT INTO test(ID, col2, col3)VALUES(1,0,0);

Let us run a transaction that is supposed to fail:

BEGIN TRANSACTION;INSERT INTO test(ID, col2, col3)VALUES(2,1,1);UPDATE test SET col3=ID/col2;COMMIT; 

ERROR: division by zero
SQL state: 22012 

After the transaction fails, there is only one row visible from the same session:

SELECT * FROM test;

1;0;0 

Also the transaction is rolled back, so there is nothing to commit:

COMMIT;WARNING:  there is no transaction in progress   

Function bodies are always run in transaction context.

We have already discussed that in the previous post. As such, any error occurring inside a function causes the function to abort, and the whole transaction to roll back. The following example shows it:

DROP TABLE test;

CREATE TABLE test(ID INT NOT NULL, CONSTRAINT PK_test PRIMARY KEY(ID),col2 INT NOT NULL, col3 FLOAT);INSERT INTO test(ID, col2, col3)VALUES(1,0,0);
CREATE OR REPLACE FUNCTION Add_Test_Rows()RETURNS VARCHAR AS$BODY$
BEGIN-- this insert succeedsINSERT INTO test(ID, col2, col3)VALUES(2,0,0);-- the first row violates the PKINSERT INTO test(ID, col2, col3)VALUES(1,0,0),(3,0,0);RETURN 'Success';END;$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT Add_Two_Test_Rows();
ERROR:  duplicate key value violates unique constraint "pk_test" DETAIL:  Key (id)=(1) already exists.

SELECT * FROM test;1;0;0

COMMIT;WARNING:  there is no transaction in progress 

Note that the error message clearly indicates the offending row - this is very convenient.

If we catch exceptions, the transaction has already rolled back.

When we've caught an exception, we can still get some diagnostics information, but the all the changes are gone. See for yourself:

CREATE OR REPLACE FUNCTION Add_Test_Rows()RETURNS VARCHAR AS$BODY$
DECLARE
  err_msg VARCHAR;
  err_detail VARCHAR;
  cnt INT;BEGIN-- this row insertsINSERT INTO test(ID, col2, col3)VALUES(2,0,0);-- this row violates the PKINSERT INTO test(ID, col2, col3)VALUES(1,0,0);RETURN 'Success';EXCEPTION
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
       err_detail = PG_EXCEPTION_DETAIL;
     cnt := (SELECT COUNT(*) FROM test);
     RETURN err_detail || ' Visible rows: ' || cnt;END;$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT Add_Test_Rows();
"Key (id)=(1) already exists. Visible rows: 1"   

As we have seen, only one row is visible when we catch the exception: the row that existed before the transaction started.