Postgres by Example: Savepoints

A savepoint is a named point inside a transaction. You can ROLLBACK TO SAVEPOINT name to undo work done after that point while keeping everything before it. Savepoints are how PostgreSQL implements nested-transaction-like semantics — useful when you want to try a risky step, recover from an error, and continue, or when application code emulates nested transactions.

What you'll learn:

• Setting a savepoint with SAVEPOINT name
• Rolling back to a savepoint
• Releasing a savepoint
• Recovering from an error inside a transaction
• The relationship to BEGIN/COMMIT

-- Setup
BEGIN;
DROP TABLE IF EXISTS sp_demo;
CREATE TABLE sp_demo (id integer PRIMARY KEY);

INSERT INTO sp_demo VALUES (1);
SAVEPOINT after_one;

INSERT INTO sp_demo VALUES (2);
SAVEPOINT after_two;

INSERT INTO sp_demo VALUES (3);

-- Undo only the last insert
ROLLBACK TO SAVEPOINT after_two;

-- After rolling back to after_two, the table has 1 and 2 (not 3)
SELECT * FROM sp_demo ORDER BY id;

-- Release a savepoint when we are sure we won't need it
RELEASE SAVEPOINT after_one;

-- Recovering from an error mid-transaction
SAVEPOINT before_risky;
-- This insert will fail (duplicate)
DO $$ BEGIN
  INSERT INTO sp_demo VALUES (2);
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'duplicate caught — continuing';
END $$;
-- Or, without the PL/pgSQL: after a failing statement, you can ROLLBACK TO before_risky and keep going.

SELECT * FROM sp_demo ORDER BY id;

COMMIT;

DROP TABLE IF EXISTS sp_demo;

SAVEPOINT name marks a point. ROLLBACK TO SAVEPOINT name undoes everything between the savepoint and now, but the transaction continues — you can keep doing work and eventually COMMIT. RELEASE SAVEPOINT name forgets the savepoint (you cannot roll back to it anymore) but does not undo any work.

Savepoints are how application frameworks emulate "nested transactions": when application code calls begin_transaction while already inside one, the driver sets a savepoint instead of starting a new top-level transaction. A "rollback" inside the inner block rolls back to that savepoint.

The error-recovery use case is important. Inside a transaction, any error puts the transaction in an "aborted" state where every subsequent statement fails until you ROLLBACK. With savepoints, you can ROLLBACK TO SAVEPOINT before_risky and continue — the transaction is back to a usable state.

To run:

$ psql -f source/savepoints.sql postgres
BEGIN
DROP TABLE
CREATE TABLE
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
ROLLBACK
 id
----
  1
  2
(2 rows)
RELEASE
 ...
COMMIT

Common pitfalls:

• Savepoints have overhead. A transaction with thousands of savepoints (a common ORM pattern) can be expensive.
• After ROLLBACK TO SAVEPOINT s, the savepoint s itself still exists — you can roll back to it again. To remove it, use RELEASE.
• RELEASE SAVEPOINT s does not "commit" the work — only COMMIT (of the enclosing transaction) does. RELEASE just removes the marker.
• Savepoints only exist inside an explicit transaction. SAVEPOINT s; outside of one is an error.

Tip: ORMs often use savepoints to implement "transactional" tests where each test runs inside a savepoint that gets rolled back, leaving the database untouched. Look up pytest-postgresql, RSpec's transactional fixtures, etc.

Try it: Add a second savepoint, insert more rows, then ROLLBACK TO the first savepoint and see all the work after it gone. Try the error-recovery pattern: BEGIN; INSERT ... SAVEPOINT s; INSERT (bad row); ROLLBACK TO SAVEPOINT s; INSERT (good row); COMMIT;.

Source: savepoints.sql

Next: CREATE VIEW

Home: Postgres by Example