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: 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:

CREATE TABLE test(ID INT NOT NULL, Some_Value VARCHAR);
BEGIN TRANSACTION;
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

BEGIN TRANSACTION;
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');

COMMIT;

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');

ROLLBACK;
  

--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:

CREATE OR REPLACE FUNCTION InsertTest(p_ID INT, P_Value VARCHAR)
RETURNS VOID AS
$BODY$
BEGIN
INSERT INTO 
test(ID, Some_Value)
VALUES(p_ID, P_Value);
COMMIT;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;

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

--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

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 at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

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