PostgreSQL 11 - Server-side Procedures (Part 2) - 2ndQuadrant

Transaction control in PL procedures

A couple of months back, I wrote about how we now have the ability to write Stored Procedures in PostgreSQL. This post follows up on that and talks about the next step that was implemented: transaction control in PL procedures. The feature was committed on 22-Jan-2018.

With this addition, you now have the ability to call COMMIT and ROLLBACK commands in PL/pgSQL from within a procedure. To illustrate:

CREATE TABLE test1 (a int);

CREATE PROCEDURE transaction_test1()
 AS $
 BEGIN
     FOR i IN 0..9 LOOP
         INSERT INTO test1 (a) VALUES (i);
         IF i % 2 = 0 THEN
             RAISE NOTICE 'i=%, txid=% will be committed', i, txid_current();
             COMMIT;
         ELSE
             RAISE NOTICE 'i=%, txid=% will be rolledback', i, txid_current();
             ROLLBACK;
         END IF;
     END LOOP;
 END
 $
 LANGUAGE PLPGSQL;

The results are as follows:

test=# CALL transaction_test1();
 NOTICE:  i=0, txid=723 will be committed
 CONTEXT:  PL/pgSQL function transaction_test1() line 6 at RAISE
 NOTICE:  i=1, txid=724 will be rolledback
 CONTEXT:  PL/pgSQL function transaction_test1() line 9 at RAISE
 NOTICE:  i=2, txid=725 will be committed
 CONTEXT:  PL/pgSQL function transaction_test1() line 6 at RAISE
 NOTICE:  i=3, txid=726 will be rolledback
 CONTEXT:  PL/pgSQL function transaction_test1() line 9 at RAISE
 NOTICE:  i=4, txid=727 will be committed
 CONTEXT:  PL/pgSQL function transaction_test1() line 6 at RAISE
 NOTICE:  i=5, txid=728 will be rolledback
 CONTEXT:  PL/pgSQL function transaction_test1() line 9 at RAISE
 NOTICE:  i=6, txid=729 will be committed
 CONTEXT:  PL/pgSQL function transaction_test1() line 6 at RAISE
 NOTICE:  i=7, txid=730 will be rolledback
 CONTEXT:  PL/pgSQL function transaction_test1() line 9 at RAISE
 NOTICE:  i=8, txid=731 will be committed
 CONTEXT:  PL/pgSQL function transaction_test1() line 6 at RAISE
 NOTICE:  i=9, txid=732 will be rolledback
 CONTEXT:  PL/pgSQL function transaction_test1() line 9 at RAISE
 CALL

test=# SELECT xmin,* FROM test1;
  xmin | a 
 ------+---
   723 | 0
   725 | 2
   727 | 4
   729 | 6
   731 | 8
 (5 rows)

Why is this important?

Well, a big driver behind getting the initial infrastructure in for PL procedures was to allow the ability to control transactions within the procedure. Functions don’t allow you to do that, they operate within a transaction. With this functionality, you will be able to operate across transactions. This allows you commit control over your transactions based on your business logic and program flow.

Very cool!

There are a few limitations. An example is that you can only use the transaction control features in a procedure called from the top level, and not one called from another procedure or function instance.

The feature implements this transaction control (in varying syntax) in each of the supplied procedural languages: PL/pgSQL, PL/Perl, PL/Python, PL/Tcl.