Savepoints

A savepoint identifies a point within the current transaction. The PostgreSQL 18 ROLLBACK TO manual documents that rollback to it discards later transactional changes without ending the transaction. Savepoints are not independently committed nested transactions: surviving changes remain part of the outer transaction.

Recovering from an error mid-transaction

This Scenario establishes a savepoint before a uniqueness violation, rolls back to it, then inserts a replacement row. The final SELECT asserts that the earlier INSERT and the replacement both committed.

A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

A> INSERT INTO items VALUES (2, 'gadget');
INSERT 0 1

A> SAVEPOINT before_risky;
SAVEPOINT

A> INSERT INTO items VALUES (3, 'widget'); -- unique_violation — the transaction is aborted
ERROR:  23505: duplicate key value violates unique constraint "items_name_key"

Without the savepoint this transaction would be doomed. With it, we rewind past the failure and carry on.

A> ROLLBACK TO SAVEPOINT before_risky;
ROLLBACK

A> INSERT INTO items VALUES (3, 'doohickey');
INSERT 0 1

A> COMMIT;
COMMIT

A> SELECT id, name FROM items ORDER BY id; -- survived — it predates the savepoint
 id |   name    
----+-----------
  1 | widget    
  2 | gadget    
  3 | doohickey 
(3 rows)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

This is recovery from a statement error with a usable prior savepoint. It is not a general substitute for retrying a serialization failure with a fresh transaction, and it cannot restore a closed connection.

Nesting and RELEASE

The Scenario rolls back to outer_sp, then asserts 3B001 when it tries inner_sp. After recovering to outer_sp again, it inserts row 4 and releases the savepoint. A subsequent rollback attempt asserts that outer_sp is gone; recovery through a newer savepoint permits COMMIT. The final rows are 1 and 4.

A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

A> INSERT INTO steps VALUES (1);
INSERT 0 1

A> SAVEPOINT outer_sp;
SAVEPOINT

A> INSERT INTO steps VALUES (2);
INSERT 0 1

A> SAVEPOINT inner_sp;
SAVEPOINT

A> INSERT INTO steps VALUES (3);
INSERT 0 1

Rolling back to the OUTER savepoint discards rows 2 and 3 — and inner_sp itself.

A> ROLLBACK TO SAVEPOINT outer_sp;
ROLLBACK

A> ROLLBACK TO SAVEPOINT inner_sp; -- no such savepoint — it was destroyed by the outer rollback
ERROR:  3B001: savepoint "inner_sp" does not exist

A> ROLLBACK TO SAVEPOINT outer_sp; -- recover from that error, same trick as before
ROLLBACK

RELEASE keeps the work done after the savepoint, but you can no longer rewind to it.

A> INSERT INTO steps VALUES (4);
INSERT 0 1

A> RELEASE SAVEPOINT outer_sp;
RELEASE

A> SAVEPOINT after_release;
SAVEPOINT

A> ROLLBACK TO SAVEPOINT outer_sp; -- RELEASE removed outer_sp; recover using the newer savepoint
ERROR:  3B001: savepoint "outer_sp" does not exist

A> ROLLBACK TO SAVEPOINT after_release;
ROLLBACK

A> COMMIT;
COMMIT

A> SELECT n FROM steps ORDER BY n;
 n 
---
 1 
 4 
(2 rows)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The ROLLBACK TO description documents that later savepoints are destroyed and the target remains usable. RELEASE SAVEPOINT removes a savepoint and later savepoints without discarding their transactional changes; it does not commit. The subtransaction manual documents increased storage I/O overhead beyond 64 open subxids (assigned nonvirtual subtransaction IDs) per backend. Read-only subtransactions receive no subxid. A write assigns one to the subtransaction and also assigns nonvirtual IDs to any ancestors that need them. No performance measurement in these Scenarios establishes a workload-specific cost.

Further reading

• PostgreSQL 18: SAVEPOINT
• The same lesson on MySQL