THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

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 succeeds
INSERT INTO test(ID, col2, col3)
VALUES(2,0,0);
-- the first row violates the PK
INSERT 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 inserts
INSERT INTO test(ID, col2, col3)
VALUES(2,0,0);
-- this row violates the PK
INSERT 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.

Published Tuesday, November 26, 2013 4:20 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement