SAVEPOINT

Objectives

By the end of this lesson, you should be able to:

• Mark a checkpoint inside a transaction with SAVEPOINT
• Undo back to a savepoint with ROLLBACK TO SAVEPOINT, without losing earlier work
• Explain how a savepoint protects a transaction from the all-or-nothing problem in the previous lesson

💡 Why this matters: The previous lesson’s failed transaction lost Erin’s perfectly valid UPDATE, just because a later statement failed. A SAVEPOINT marks a point to fall back to, undoing only what happened after it, keeping everything before it intact.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the accounts table from the previous lesson (Erin Castillo at 400.00, Jordan Blake at 300.00).

Creating a Savepoint

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE owner = 'Erin Castillo';

SELECT owner, balance FROM accounts ORDER BY owner;

     owner       | balance
------------------+---------
 Erin Castillo   | 350.00
 Jordan Blake    | 300.00

SAVEPOINT before_risky_update;

SAVEPOINT before_risky_update marks a named checkpoint, right after Erin’s balance was successfully updated, everything before this point can be preserved even if something afterward goes wrong.

The Risky Statement Fails

UPDATE accounts SET balance = balance - 9999 WHERE owner = 'Jordan Blake';

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"

Same failure as the previous lesson, this violates the CHECK constraint. Without a savepoint, the entire transaction would now be aborted, forcing a full ROLLBACK that also undoes Erin’s update.

ROLLBACK TO SAVEPOINT Recovers Just the Failed Part

ROLLBACK TO SAVEPOINT before_risky_update;

SELECT owner, balance FROM accounts ORDER BY owner;

     owner       | balance
------------------+---------
 Erin Castillo   | 350.00
 Jordan Blake    | 300.00

ROLLBACK TO SAVEPOINT before_risky_update undoes everything since the savepoint, the failed update, and clears the transaction’s aborted state, but Erin’s earlier update is untouched, still 350.00. The transaction is no longer aborted, it can continue normally from here.

Finishing the Transaction

COMMIT;

SELECT owner, balance FROM accounts ORDER BY owner;

     owner       | balance
------------------+---------
 Erin Castillo   | 350.00
 Jordan Blake    | 300.00

COMMIT succeeds normally, making Erin’s update permanent. Jordan’s balance stays at 300.00, exactly as it should, the risky update never actually took effect, and thanks to the savepoint, this one failure didn’t force undoing Erin’s valid change along with it.

Multiple Savepoints

A transaction can define more than one savepoint, at different points along its progress, and roll back to any of them by name, undoing everything after that specific point while keeping everything before it. This is useful for a transaction with several independent steps, each one protected from the others’ potential failures.

Try It

1. Start a transaction, update a balance, create a savepoint, then run a second update that violates the CHECK constraint.
2. Use ROLLBACK TO SAVEPOINT to recover, and confirm the first update is still intact.
3. Commit the transaction and confirm only the first update was made permanent.
4. Explain, in your own words, the difference between ROLLBACK (previous lesson) and ROLLBACK TO SAVEPOINT (this lesson), specifically what each one discards.

Recap

• SAVEPOINT name marks a checkpoint inside a transaction, without ending it.
• ROLLBACK TO SAVEPOINT name undoes everything since that savepoint, keeping earlier changes in the transaction intact, and clears an aborted state so the transaction can continue.
• Multiple savepoints can exist in one transaction, each protecting the work that came before it from a later failure.

Next lesson: this module’s exercises, performing a real account transfer, rolling back on purpose, and using savepoints to recover from a partial failure.