By John Doe May 19, 2024
Summary: In this article, we will learn what’s the relationship between PL/pgSQL exception and subtransactions.
Table of Contents
Introduction
BEGIN / EXCEPTION WHEN .. / END blocks in PL/pgSQL are implemented with subtransactions, hence an EXCEPTION block in a PL/pgSQL function will increment the transaction id (xid).
Why PL/pgSQL needs subtransactions (or savepoints) to handle exceptions?
Well, if you think about when you catch an exception you probably want to resume your execution, that is you must have a way to rollback your unit of work and start over again.
Getting ready for testing
It is possible to inspect the transactions in action with a simple function and a table to abuse. Let’s see the function:
CREATE OR REPLACE FUNCTION f_loop(b int DEFAULT 0, e int DEFAULT 10)
RETURNS void AS $$
BEGIN
RAISE DEBUG 'TXID of the function (here should not be assigned) function: % %',
txid_current_if_assigned(),
txid_status(txid_current_if_assigned());
FOR f IN b .. e LOOP
BEGIN
RAISE DEBUG 'Before INSERT of % TXID: % SNAPSHOT: %',
f,
txid_current_if_assigned(),
txid_current_snapshot();
INSERT INTO foo( i ) VALUES( f );
RAISE DEBUG 'After INSERT of % TXID: % SNAPSHOT: %',
f,
txid_current_if_assigned(),
txid_current_snapshot();
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
RAISE DEBUG 'Exception for % TXID: % SNAPSHOT: %',
f,
txid_current_if_assigned(),
txid_current_snapshot();
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;
The function accepts a begin and end indexes and loop thru every value between them, trying to insert the value into a table. At every step, including the exception, we inspect txid_current_if_assigned(), that reports the transaction ID (xid) and txid_current_snapshot() that provides the current snapshot, that means roughly the minimum and maximum xid this transaction is “flying” over.
The definition of the table is pretty straightforward: it has a single column with a UNIQUE constraint on it. That’s the constraint the function is going to violate.
CREATE TABLE foo (i int PRIMARY KEY);
First Run: No Exceptions
Since the table is empty, inserting values from 1 to 10 does not produce any exception.
testdb=> SELECT f_loop( 1, 10 );
DEBUG: TXID of the function (here should not be assigned) function: <NULL> <NULL>
DEBUG: Before INSERT of 1 TXID: <NULL> SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 1 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: Before INSERT of 2 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 2 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: Before INSERT of 3 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 3 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: Before INSERT of 4 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 4 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: Before INSERT of 5 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 5 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: Before INSERT of 6 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 6 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: Before INSERT of 7 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 7 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: Before INSERT of 8 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 8 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: Before INSERT of 9 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 9 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: Before INSERT of 10 TXID: 4748 SNAPSHOT: 4748:4748:
DEBUG: After INSERT of 10 TXID: 4748 SNAPSHOT: 4748:4748:
f_loop
--------
(1 row)
In the very first run the xid is NULL because the function has not (yet) modified anything. That’s why I use txid_current_if_assigned() instead of txid_current() to avoid wasting a number. Once the function starts modifying the data (i.e., after the very first INSERT) the transaction is promoted from virtual to concrete and so a xid is assigned.
Since no exception at all is raised, the xid of the function is fixed and so is the snapshot.
Second Run: Half of Exceptions
Let’s run it with some numbers overlapping, so that half of the values are inserted succesfully and half throw an exception.
testdb=> SELECT f_loop( 5, 15 );
DEBUG: TXID of the function (here should not be assigned) function: <NULL> <NULL>
DEBUG: Before INSERT of 5 TXID: <NULL> SNAPSHOT: 4760:4760:
DEBUG: Exception for 5 TXID: 4760 SNAPSHOT: 4760:4762:
DEBUG: Before INSERT of 6 TXID: 4760 SNAPSHOT: 4760:4762:
DEBUG: Exception for 6 TXID: 4760 SNAPSHOT: 4760:4763:
DEBUG: Before INSERT of 7 TXID: 4760 SNAPSHOT: 4760:4763:
DEBUG: Exception for 7 TXID: 4760 SNAPSHOT: 4760:4764:
DEBUG: Before INSERT of 8 TXID: 4760 SNAPSHOT: 4760:4764:
DEBUG: Exception for 8 TXID: 4760 SNAPSHOT: 4760:4765:
DEBUG: Before INSERT of 9 TXID: 4760 SNAPSHOT: 4760:4765:
DEBUG: Exception for 9 TXID: 4760 SNAPSHOT: 4760:4766:
DEBUG: Before INSERT of 10 TXID: 4760 SNAPSHOT: 4760:4766:
DEBUG: Exception for 10 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: Before INSERT of 11 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: After INSERT of 11 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: Before INSERT of 12 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: After INSERT of 12 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: Before INSERT of 13 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: After INSERT of 13 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: Before INSERT of 14 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: After INSERT of 14 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: Before INSERT of 15 TXID: 4760 SNAPSHOT: 4760:4767:
DEBUG: After INSERT of 15 TXID: 4760 SNAPSHOT: 4760:4767:
f_loop
--------
(1 row)
As you can see, in the first five numbers there’s an exception reported. The xid of the function remains the same, but the snapshot grows by 6 transactions identifiers (one for the function, five for the subtransactions). After that, the remaining five values are succesfully inserted and so the snapshot does not grow anymore.
Where are these Subtransactions?
If you now inspect the MVCC values for the table, you can see that every value inserted has a different transaction id xmin, without any regard to the fact that the function call did catch an exception or not.
SELECT xmin, xmax, cmin, cmax, * FROM foo;
xmin | xmax | cmin | cmax | i
------|------|------|------|----
4749 | 0 | 0 | 0 | 1
4750 | 0 | 1 | 1 | 2
4751 | 0 | 2 | 2 | 3
4752 | 0 | 3 | 3 | 4
4753 | 0 | 4 | 4 | 5
4754 | 0 | 5 | 5 | 6
4755 | 0 | 6 | 6 | 7
4756 | 0 | 7 | 7 | 8
4757 | 0 | 8 | 8 | 9
4758 | 0 | 9 | 9 | 10
4767 | 0 | 6 | 6 | 11
4768 | 0 | 7 | 7 | 12
4769 | 0 | 8 | 8 | 13
4770 | 0 | 9 | 9 | 14
4771 | 0 | 10 | 10 | 15
(15 rows)
How to Simulate the Same Behavior
Savepoints do pretty much the same! Therefore, let’s truncate the table and insert new values in it with an explicit transaction and savepoints:
TRUNCATE foo;
BEGIN;
INSERT INTO foo(i) VALUES(1);
SAVEPOINT S1;
INSERT INTO foo(i) VALUES(2);
SAVEPOINT S2;
INSERT INTO foo(i) VALUES(3);
SAVEPOINT S3;
COMMIT;
SELECT xmin,xmax, cmin, cmax, * FROM foo;
xmin | xmax | cmin | cmax | i
------|------|------|------|---
4779 | 0 | 0 | 0 | 1
4780 | 0 | 1 | 1 | 2
4781 | 0 | 2 | 2 | 3
(3 rows)
As you can see the xmin is incremented continuously by every INSERT.
Conclusion
Exception are quite clearly implemented in PL/pgSQL (and possibly in other languages) by means of subtransactions. Thus, you need to beware of PL/pgSQL functions or triggers with exception blocks.