Subtransactions Release Rollback Savepoint

Demonstrating functionality of PostgreSQL ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT.

This article is a demonstration of ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT subtransaction overflow behaviour discussed in substransactions-introduction.
All tests in this article are on EPAS 15.1 compiled from source with Dilip Kumar's monitoring patch.
As discussed in the previous article, the behaviour of ROLLBACK TO SAVEPOINT AND RELEASE SAVEPOINT is as follows:

ROLLBACK TO SAVEPOINT

ROLLBACK TO SAVEPOINT discards the effect of all commands that were run after that savepoint.
ROLLBACK TO SAVEPOINT can be used to prevent the number of subtransactions ever exceeding 64, preventing overflow, at the cost of losing any commands executed after the savepoint.
If the number of subtransactions ever exceeds 64, the transaction stays in overflow state, and rolling back to earlier transactions does not remove overflow.

RELEASE SAVEPOINT

RELEASE SAVEPOINT does not prevent subtransactions contributing to the PGPROC_MAX_CACHED_SUBXIDS overflow limit and cannot prevent overflow.

Please see the below tests that demonstrate this behaviour:

Tests

Test 1 - "rollback to savepoint" test - 64 savepoints

connection 1:

SQL script contains 64 savepoints

DROP TABLE IF EXISTS exists savepoint_test;
CREATE TABLE savepoint_test (test INTEGER);
BEGIN;
INSERT INTO savepoint_test VALUES(generate_series(1,100));
SAVEPOINT aa;
INSERT INTO savepoint_test VALUES(generate_series(101,200));
SAVEPOINT ab;
INSERT INTO savepoint_test VALUES(generate_series(201,300));
SAVEPOINT ac;
INSERT INTO savepoint_test VALUES(generate_series(301,400));
SAVEPOINT ad;
INSERT INTO savepoint_test VALUES(generate_series(401,500));
SAVEPOINT ae;
INSERT INTO savepoint_test VALUES(generate_series(501,500));
SAVEPOINT af;
INSERT INTO savepoint_test VALUES(generate_series(601,700));
SAVEPOINT ag;
INSERT INTO savepoint_test VALUES(generate_series(701,800));
SAVEPOINT ah;
INSERT INTO savepoint_test VALUES(generate_series(801,900));
SAVEPOINT ai;
INSERT INTO savepoint_test VALUES(generate_series(901,1000));
SAVEPOINT aj;
INSERT INTO savepoint_test VALUES(generate_series(1001,1100));
SAVEPOINT ak;
INSERT INTO savepoint_test VALUES(generate_series(1101,1200));
SAVEPOINT al;
INSERT INTO savepoint_test VALUES(generate_series(1201,1300));
SAVEPOINT am;
INSERT INTO savepoint_test VALUES(generate_series(1401,1500));
SAVEPOINT an;
INSERT INTO savepoint_test VALUES(generate_series(1501,1600));
SAVEPOINT ao;
INSERT INTO savepoint_test VALUES(generate_series(1601,1700));
SAVEPOINT ap;
INSERT INTO savepoint_test VALUES(generate_series(1701,1800));
SAVEPOINT aq;
INSERT INTO savepoint_test VALUES(generate_series(1801,1900));
SAVEPOINT ar;
INSERT INTO savepoint_test VALUES(generate_series(1901,2000));
SAVEPOINT av;
INSERT INTO savepoint_test VALUES(generate_series(2001,2100));
SAVEPOINT aw;
INSERT INTO savepoint_test VALUES(generate_series(2101,2200));
SAVEPOINT ax;
INSERT INTO savepoint_test VALUES(generate_series(2201,2300));
SAVEPOINT ay;
INSERT INTO savepoint_test VALUES(generate_series(2301,2400));
SAVEPOINT az;
INSERT INTO savepoint_test VALUES(generate_series(2401,2500));
SAVEPOINT ba;
INSERT INTO savepoint_test VALUES(generate_series(2501,2600));
SAVEPOINT bb;
INSERT INTO savepoint_test VALUES(generate_series(2601,2700));
SAVEPOINT bc;
INSERT INTO savepoint_test VALUES(generate_series(2701,2800));
SAVEPOINT bd;
INSERT INTO savepoint_test VALUES(generate_series(2801,2900));
SAVEPOINT be;
INSERT INTO savepoint_test VALUES(generate_series(2901,3000));
SAVEPOINT bf;
INSERT INTO savepoint_test VALUES(generate_series(3001,3100));
SAVEPOINT bg;
INSERT INTO savepoint_test VALUES(generate_series(3101,3200));
SAVEPOINT bh;
INSERT INTO savepoint_test VALUES(generate_series(3201,3300));
SAVEPOINT bi;
INSERT INTO savepoint_test VALUES(generate_series(3301,3400));
SAVEPOINT bj;
INSERT INTO savepoint_test VALUES(generate_series(3401,3500));
SAVEPOINT bk;
INSERT INTO savepoint_test VALUES(generate_series(3501,3600));
SAVEPOINT bl;
INSERT INTO savepoint_test VALUES(generate_series(3601,3700));
SAVEPOINT bm;
INSERT INTO savepoint_test VALUES(generate_series(3701,3800));
SAVEPOINT bn;
INSERT INTO savepoint_test VALUES(generate_series(3801,3900));
SAVEPOINT bo;
INSERT INTO savepoint_test VALUES(generate_series(4001,4100));
SAVEPOINT bp;
INSERT INTO savepoint_test VALUES(generate_series(4101,4200));
SAVEPOINT bq;
INSERT INTO savepoint_test VALUES(generate_series(4201,4300));
SAVEPOINT br;
INSERT INTO savepoint_test VALUES(generate_series(4301,4400));
SAVEPOINT bs;
INSERT INTO savepoint_test VALUES(generate_series(4401,4500));
SAVEPOINT bt;
INSERT INTO savepoint_test VALUES(generate_series(4501,4600));
SAVEPOINT bu;
INSERT INTO savepoint_test VALUES(generate_series(4601,4700));
SAVEPOINT bv;
INSERT INTO savepoint_test VALUES(generate_series(4701,4800));
SAVEPOINT bw;
INSERT INTO savepoint_test VALUES(generate_series(4801,4900));
SAVEPOINT bx;
INSERT INTO savepoint_test VALUES(generate_series(4901,5000));
SAVEPOINT by;
INSERT INTO savepoint_test VALUES(generate_series(5001,5100));
SAVEPOINT bz;
INSERT INTO savepoint_test VALUES(generate_series(5101,5200));
SAVEPOINT ca;
INSERT INTO savepoint_test VALUES(generate_series(5201,5300));
SAVEPOINT cb;
INSERT INTO savepoint_test VALUES(generate_series(5301,5400));
SAVEPOINT cc;
INSERT INTO savepoint_test VALUES(generate_series(5401,5500));
SAVEPOINT cd;
INSERT INTO savepoint_test VALUES(generate_series(5501,5600));
SAVEPOINT ce;
INSERT INTO savepoint_test VALUES(generate_series(5601,5700));
SAVEPOINT cf;
INSERT INTO savepoint_test VALUES(generate_series(5701,5800));
SAVEPOINT cg;
INSERT INTO savepoint_test VALUES(generate_series(5901,6000));
SAVEPOINT ch;
INSERT INTO savepoint_test VALUES(generate_series(6001,6100));
SAVEPOINT ci;
INSERT INTO savepoint_test VALUES(generate_series(6101,6200));
SAVEPOINT cj;
INSERT INTO savepoint_test VALUES(generate_series(6201,6300));
SAVEPOINT ck;
INSERT INTO savepoint_test VALUES(generate_series(6301,6400));
SAVEPOINT cl;
INSERT INTO savepoint_test VALUES(generate_series(6401,6500));
SAVEPOINT cm;
INSERT INTO savepoint_test VALUES(generate_series(6501,6600));
SAVEPOINT cn;
INSERT INTO savepoint_test VALUES(generate_series(6601,6700));
SAVEPOINT co;
INSERT INTO savepoint_test VALUES(generate_series(6701,6800));
SAVEPOINT cp;

connection 2:

246175 transaction at 64 subtransactions, not overflowed:


edb=# SELECT id, pg_catalog.pg_stat_get_backend_pid(id) as pid, pg_catalog.pg_stat_get_backend_subxact(id) AS nsubxact FROM pg_catalog.pg_stat_get_backend_idset() AS id;
id | pid | nsubxact 
1 | 106623 | (0,f)
2 | 106624 | (0,f)
3 | 106625 | (0,f)
4 | 246175 | (64,f)
5 | 107144 | (0,f)
6 | 106620 | (0,f)
7 | 106619 | (0,f)
8 | 106622 | (0,f)

connection 1:

ROLLBACK TO aa;

connection 2:

246175 subtransaction count has been reset:

edb=# SELECT id, pg_catalog.pg_stat_get_backend_pid(id) AS pid, pg_catalog.pg_stat_get_backend_subxact(id) AS nsubxact FROM pg_catalog.pg_stat_get_backend_idset() AS id;

id | pid | nsubxact 
1 | 106623 | (0,f)
2 | 106624 | (0,f)
3 | 106625 | (0,f)
4 | 246175 | (0,f)
5 | 107144 | (0,f)
6 | 106620 | (0,f)
7 | 106619 | (0,f)
8 | 106622 | (0,f)

connection 1:

SQL script contains 32 savepoints (body omitted)

INSERT INTO savepoint_test VALUES(generate_series(101,200));
SAVEPOINT ab;
...
INSERT INTO savepoint_test VALUES(generate_series(3301,3400));
SAVEPOINT bj;

connection 2:

edb=# SELECT id, pg_catalog.pg_stat_get_backend_pid(id) AS pid, pg_catalog.pg_stat_get_backend_subxact(id) AS nsubxact FROM pg_catalog.pg_stat_get_backend_idset() AS id;

id | pid | nsubxact 
1 | 106623 | (0,f)
2 | 106624 | (0,f)
3 | 106625 | (0,f)
4 | 246175 | (32,f)
5 | 107144 | (0,f)
6 | 106620 | (0,f)
7 | 106619 | (0,f)
8 | 106622 | (0,f)

Even after an additional 32 subtransactions, the transaction is still not overflowed.

As the number of subtransactions never exceeded 64, overflow was prevented through the rollback (at the cost of losing any commands executed after the savepoint).

Test 2 - "ROLLBACK TO SAVEPOINT" test - 65 savepoints

connection 1:

SQL script contains 65 savepoints (body omitted)

DROP TABLE IF EXISTS savepoint_test;
CREATE TABLE savepoint_test (test integer);
BEGIN;
INSERT INTO savepoint_test VALUES (generate_series(1,100));
...
INSERT INTO savepoint_test VALUES(generate_series(6701,6800));
INSERT cp;
insert INTO savepoint_test VALUES(generate_series(6801,6900));
savepoint cq;

connection 2:

246175 Overflowed:

edb=# SELECT id, pg_catalog.pg_stat_get_backend_pid(id) AS pid,
pg_catalog.pg_stat_get_backend_subxact(id) AS nsubxact
FROM pg_catalog.pg_stat_get_backend_idset() AS id;

id | pid | nsubxact 
1 | 106623 | (0,f)
2 | 106624 | (0,f)
3 | 106625 | (0,f)
4 | 246175 | (64,t)
5 | 107144 | (0,f)
6 | 106620 | (0,f)
7 | 106619 | (0,f)
8 | 106622 | (0,f)

connection 1:

ROLLBACK TO aa;

connection 2:

246175 still overflowed:

edb=# SELECT id, pg_catalog.pg_stat_get_backend_pid(id) AS pid,
pg_catalog.pg_stat_get_backend_subxact(id) AS nsubxact
FROM pg_catalog.pg_stat_get_backend_idset() AS id;
id | pid | nsubxact 
1 | 106623 | (0,f)
2 | 106624 | (0,f)
3 | 106625 | (0,f)
4 | 246175 | (0,t)
5 | 107144 | (0,f)
6 | 106620 | (0,f)
7 | 106619 | (0,f)
8 | 106622 | (0,f)

As the transaction reached over 64 subtransactions the transaction is permanently overflowed until it is committed/aborted.

Test 3 - "RELEASE SAVEPOINT" test

note - if using multiple savepoints in a transaction, releasing a savepoint also releases all savepoints that occurred after it.

connection 1:

SQL script executes contains 64 savepoints, then releases all savepoints (body omitted):

DROP TABLE IF EXTS savepoint_test;
CREATE TABLE savepoint_test (test INTEGER);
BEGIN;
INSERT INTO savepoint_test VALUES(generate_series(1,100));
SAVEPOINT aa;
...
INSERT INTO savepoint_test VALUES(generate_series(6701,6800));
SAVEPOINT cp;
RELEASE aa;

connection 2:

PID 133779 still at 64 subtransactions:

edb=# SELECT id, pg_catalog.pg_stat_get_backend_pid(id) AS pid,
pg_catalog.pg_stat_get_backend_subxact(id) AS nsubxact
FROM pg_catalog.pg_stat_get_backend_idset() AS id;
id | pid | nsubxact 
1 | 106623 | (0,f)
2 | 106624 | (0,f)
3 | 106625 | (0,f)
4 | 246175 | (64,f)
5 | 107144 | (0,f)
6 | 106620 | (0,f)
7 | 106619 | (0,f)
8 | 106622 | (0,f)

connection 1:

Further savepoints inserted:

INSERT INTO savepoint_test VALUES(generate_series(6801,6900));
SAVEPOINT cq;
INSERT INTO savepoint_test VALUES(generate_series(7001,7100));
SAVEPOINT cr;

connection 2:

PID 133779 overflowed:

SELECT id, pg_catalog.pg_stat_get_backend_pid(id) AS pid,
pg_catalog.pg_stat_get_backend_subxact(id) AS nsubxact
FROM pg_catalog.pg_stat_get_backend_idset() AS id;

id | pid | nsubxact 
1 | 106623 | (0,f)
2 | 106624 | (0,f)
3 | 106625 | (0,f)
4 | 246175 | (64,t)
5 | 107144 | (0,f)
6 | 106620 | (0,f)
7 | 106619 | (0,f)
8 | 106622 | (0,f)

These tests demonstrate the ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT behaviour.

Search

ROLLBACK TO SAVEPOINT

RELEASE SAVEPOINT

Tests

Test 1 - "rollback to savepoint" test - 64 savepoints

Test 2 - "ROLLBACK TO SAVEPOINT" test - 65 savepoints

Test 3 - "RELEASE SAVEPOINT" test

Was this article helpful?