# Savepoints

> Roll back part of a transaction and recover from errors with savepoints.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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`

## Example {#example}

```sql {num="47.1" caption="Savepoints"}
-- 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.

## Run the example {#run}

To run:

```text
$ 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 {#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] 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.

> [!NOTE] 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;`.

## Downloads {#downloads}

SQL file: [savepoints.sql](/examples/source/savepoints.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)