• PostgreSQL emulates nested transactions with savepoints. A savepoint records a position within an open transaction; it allows selective rolllback without aborting the outer transaction.
• Key commands:
  • SAVEPOINT name: create a rollback marker
  • ROLLBACK TO [SAVEPOINT] name: undo all work after that marker and discard any savepoints defined since then
  • RELEASE SAVEPOINT name: remove the marker without affecting data, preventing future rollbacks to it

Schema and initial state

postgres=# \d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 name   | text    | 

postgres=# SELECT * FROM test;
 id | name 
----+------
(0 rows)

Working with multiple savepoints

postgres=# BEGIN;
BEGIN

postgres=# INSERT INTO test VALUES (10, 'alpha');
INSERT 0 1
postgres=# SAVEPOINT sp_alpha;
SAVEPOINT
postgres=# SELECT * FROM test;
 id | name  
----+--------
 10 | alpha
(1 row)

postgres=# INSERT INTO test VALUES (20, 'beta');
INSERT 0 1
postgres=# SAVEPOINT sp_beta;
SAVEPOINT
postgres=# SELECT * FROM test;
 id | name  
----+--------
 10 | alpha
 20 | beta
(2 rows)

postgres=# INSERT INTO test VALUES (30, 'gamma');
INSERT 0 1
postgres=# SELECT * FROM test;
 id | name  
----+--------
 10 | alpha
 20 | beta
 30 | gamma
(3 rows)

Rollback to a savepoint (later changes are undone and later savepoints are discarded)

postgres=# ROLLBACK TO sp_beta;
ROLLBACK
postgres=# SELECT * FROM test;
 id | name  
----+--------
 10 | alpha
 20 | beta
(2 rows)

postgres=# ROLLBACK TO sp_alpha;
ROLLBACK
postgres=# SELECT * FROM test;
 id | name  
----+--------
 10 | alpha
(1 row)

postgres=# ROLLBACK TO sp_beta;
ERROR:  no such savepoint

Releasing a savepoint (cannot roll back to a released marker)

postgres=# SELECT * FROM test;
 id | name 
----+------
(0 rows)

postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO test VALUES (1, 'a');
INSERT 0 1
postgres=# SAVEPOINT sp_a;
SAVEPOINT
postgres=# SELECT * FROM test;
 id | name 
----+------
  1 | a
(1 row)

postgres=# INSERT INTO test VALUES (2, 'b');
INSERT 0 1
postgres=# SAVEPOINT sp_b;
SAVEPOINT
postgres=# SELECT * FROM test;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

postgres=# RELEASE SAVEPOINT sp_a;
RELEASE
postgres=# SELECT * FROM test;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

postgres=# ROLLBACK TO sp_a;
ERROR:  no such savepoint

• Rolling back to a savepoint reverts all changes made after it and removes any savepoints created after that point.
• Releasing a savepoint only removes the rollback marker; it does not revert changes and makes that savepoint unusible for future rollbacks.