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.