THE SQL Server Blog Spot on the Web

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

Alexander Kuznetsov

Learning PostgreSql: No Nested Transactions, No Transactions in PL/pgSQL

Transactions and error handling in PostgreSql are substantially different. We shall discuss only the features that we needed to learn to complete our project. It will take us more than one post to describe them all.

TL;DR; Transactions and error handling in PostgreSql are much simpler, but there are enough features necessary to implement robust error handling without too much learning.

No Nested Transactions

We do not use nested transactions in SQL Server, because we have never seen any practical need. From my perspective, lack of nested transactions in PostgreSql simply means that we have less to learn and less to be careful about.

The following script demonstrates lack of nested transactions in PostgreSql:

INSERT INTO test(ID, Some_Value)
VALUES(1, 'After the first BEGIN TRANSACTION');

In SQL Server, we could issue another BEGIN TRANSACTION, and it would do nothing, except for incrementing @@TRANCOUNT. Instead, the following command raises a warning

WARNING:  there is already a transaction in progress

At this point, this table has no committed rows - we can select from another session and see for ourselves.

The following script does not decrement @@TRANCOUNT - it actually commits, as we can see from another session:

INSERT INTO test(ID, Some_Value)
VALUES(2, 'After the second BEGIN TRANSACTION');


Should we run the following script on SQL Server, all the inserted rows would be rolled back, and the table would be empty. However, there is no outstanding transaction - it has just been committed. So, the INSERT runs as a standalone transaction. The ROLLBACK fails because there is no outstanding transaction:

INSERT INTO test(ID, Some_Value)
VALUES(3, 'After the first COMMIT');


--NOTICE:  there is no transaction in progress  

As we have seen, one and the same SQL may run differently on PostgreSql and SQL Server, because

We Cannot Begin/End Transactions in PL/pgSQL

If we wrap DML in a function, and invoke that function, of course the function call runs in a transaction. Because there are no nested transactions, it makes no sense to allow BEGIN TRANSACTION and COMMIT in functions. The following script shows a runtime error:

test(ID, Some_Value)
VALUES(p_ID, P_Value);

SELECT InsertTest(p_ID := 5, P_Value := 'Added by InsertTest');

--ERROR:  cannot begin/end transactions in PL/pgSQL

There is more to transactions and error handling. We shall continue later, so stay tuned.

Published Monday, November 25, 2013 10:58 AM by Alexander Kuznetsov



Dan S said:

Maybe you could use savepoints .


Best Regards

Dan S

June 14, 2014 4:16 AM

Alexander Kuznetsov said:


I am trying very hard to design and implements systems to be as simple as possible, so that they do not need advanced features too much, unless it is absolutely necessary.

Currently we do not use savepoints anywhere in any of our systems, to keep them simple.

July 11, 2014 4:54 PM

David said:

Yes, you can't start a transaction inside a plpgsql function, but that is, because any plpgsql function IS a transaction by itself when called.

February 13, 2015 12:39 PM

Yuriy Shcherbak said:

For Postgresql 9.5 or newer you can use dynamic background workers provided by pg_background extension. It creates autonomous transaction. Please, refer the [github page][1] of the extension. The sollution is better then db_link. There is a complete guide on [Autonomous transaction support in PostgreSQL][2]



March 3, 2017 6:23 PM
New Comments to this post are disabled

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, 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 and Currently he works as an agile developer.

This Blog


Privacy Statement