Savepoints

In the previous lesson, you learned that ROLLBACK undoes an entire transaction. But what if you want to undo only part of a transaction while keeping the rest? Savepoints give you that control. They act as bookmarks within a transaction that you can roll back to without losing all your work.

What is a Savepoint?

A savepoint is a named marker within a transaction. You can create multiple savepoints as your transaction progresses. If something goes wrong, you can roll back to any savepoint, undoing only the work done after that point. The transaction remains open, and you can continue working.

Think of savepoints like the save points in a video game. If you fail at a challenge, you reload from the last save rather than starting the entire game over.

Creating a Savepoint

Use SAVEPOINT name to create a savepoint:

BEGIN;

UPDATE products SET price = price * 1.10 WHERE category = 'Electronics';

SAVEPOINT after_electronics;

UPDATE products SET price = price * 1.05 WHERE category = 'Books';

SAVEPOINT after_books;

UPDATE products SET price = price * 1.20 WHERE category = 'Toys';

At this point, there are three updates and two savepoints in the transaction. None of the changes are committed yet.

Rolling Back to a Savepoint

ROLLBACK TO SAVEPOINT name undoes all changes made after the named savepoint. The transaction stays open.

BEGIN;

UPDATE products SET price = price * 1.10 WHERE category = 'Electronics';

SAVEPOINT after_electronics;

UPDATE products SET price = price * 1.05 WHERE category = 'Books';

-- Hmm, the Books price increase was wrong. Undo it.
ROLLBACK TO SAVEPOINT after_electronics;

-- The Electronics update is still intact.
-- The Books update has been undone.
-- We can now try a different update for Books.
UPDATE products SET price = price * 1.02 WHERE category = 'Books';

COMMIT;
-- Electronics: +10%, Books: +2%

The key insight: rolling back to a savepoint does not end the transaction. You can keep working, create new savepoints, and eventually commit.

A Complete Walkthrough

Let us walk through a detailed example with the products table:

BEGIN;

-- Step 1: Update Widget price
UPDATE products SET price = 29.99 WHERE name = 'Widget';
SAVEPOINT sp_widget;

-- Step 2: Update Gadget price
UPDATE products SET price = 49.99 WHERE name = 'Gadget';
SAVEPOINT sp_gadget;

-- Step 3: Update all quantities (oops, this was a mistake)
UPDATE products SET quantity = 0;

-- That was wrong! Roll back to after the Gadget update
ROLLBACK TO SAVEPOINT sp_gadget;

-- Step 3 (retry): Update only out-of-stock items
UPDATE products SET quantity = 10 WHERE quantity = 0;

-- Everything looks good now
COMMIT;

After this transaction:

• Widget price is 29.99 (from Step 1, preserved)
• Gadget price is 49.99 (from Step 2, preserved)
• The mass quantity reset from Step 3 was undone
• Only previously out-of-stock items got quantity = 10

Rolling Back to Earlier Savepoints

When you roll back to a savepoint, all savepoints created after it are destroyed:

BEGIN;

INSERT INTO products (name, category, price, in_stock, quantity) VALUES ('Alpha', 'Test', 10, true, 5);
SAVEPOINT sp1;

INSERT INTO products (name, category, price, in_stock, quantity) VALUES ('Beta', 'Test', 20, true, 5);
SAVEPOINT sp2;

INSERT INTO products (name, category, price, in_stock, quantity) VALUES ('Gamma', 'Test', 30, true, 5);
SAVEPOINT sp3;

-- Roll back to sp1: undoes Beta, Gamma, and destroys sp2 and sp3
ROLLBACK TO SAVEPOINT sp1;

-- sp2 no longer exists
ROLLBACK TO SAVEPOINT sp2;
-- ERROR: savepoint "sp2" does not exist

-- But sp1 still exists and Alpha is still inserted
COMMIT;
-- Only Alpha is in the table

Releasing Savepoints

RELEASE SAVEPOINT name destroys a savepoint without rolling back to it. This is used when you know you will not need to roll back to that point anymore.

BEGIN;

UPDATE products SET price = price * 1.10 WHERE category = 'Electronics';
SAVEPOINT sp1;

UPDATE products SET price = price * 1.05 WHERE category = 'Books';

-- We're confident the Books update is correct. Release the savepoint.
RELEASE SAVEPOINT sp1;

-- We can no longer ROLLBACK TO sp1
-- Both updates are still part of the transaction (not yet committed)

COMMIT;

Releasing a savepoint is mainly a resource management technique. In transactions with many savepoints, releasing ones you no longer need frees up internal resources.

Note: RELEASE SAVEPOINT does not commit anything. The changes are still part of the uncommitted transaction. Only COMMIT makes them permanent.

Error Recovery with Savepoints

One of the most valuable uses of savepoints is handling errors within a transaction. Remember that PostgreSQL aborts the entire transaction on any error. Savepoints let you recover from errors without losing all your work.

BEGIN;

-- First update succeeds
UPDATE products SET price = 99.99 WHERE name = 'Widget';

-- Create a savepoint before a risky operation
SAVEPOINT before_risky;

-- This will cause an error (invalid syntax for numeric)
UPDATE products SET price = 'invalid' WHERE name = 'Gadget';
-- ERROR: invalid input syntax for type numeric

-- Without a savepoint, the transaction would be aborted.
-- But we can roll back to before the error:
ROLLBACK TO SAVEPOINT before_risky;

-- The transaction is alive again! The Widget update is still intact.
-- Try the correct update
UPDATE products SET price = 149.99 WHERE name = 'Gadget';

COMMIT;
-- Widget: 99.99, Gadget: 149.99

This pattern is extremely useful in application code where you might attempt an operation that could fail (like inserting a row that might violate a unique constraint):

BEGIN;

SAVEPOINT before_insert;

INSERT INTO products (name, category, price, in_stock, quantity)
VALUES ('Widget', 'Gadgets', 25.00, true, 100);
-- Might fail if 'Widget' already exists (unique constraint)

-- In application code, you'd catch the error and:
ROLLBACK TO SAVEPOINT before_insert;

-- Then try an update instead:
UPDATE products SET price = 25.00, quantity = 100
WHERE name = 'Widget';

COMMIT;

Nested Savepoints Pattern

While PostgreSQL does not support true nested transactions, you can simulate them with savepoints:

BEGIN;

-- "Outer transaction" work
UPDATE products SET in_stock = true WHERE quantity > 0;

SAVEPOINT outer_save;

  -- "Inner transaction" work
  UPDATE products SET price = price * 0.90 WHERE category = 'Clearance';

  SAVEPOINT inner_save;

    -- "Deeply nested" work
    UPDATE products SET quantity = 0 WHERE name = 'Discontinued Item';

    -- Something went wrong at the deepest level
    ROLLBACK TO SAVEPOINT inner_save;
    -- Only the "Discontinued Item" update is undone

  -- The Clearance discount is still applied

-- Everything up to outer_save is preserved

COMMIT;

Practical Error Handling Pattern

Here is a pattern commonly used in application code (shown as SQL for clarity):

BEGIN;

-- Step 1: Safe operation
UPDATE products SET quantity = quantity - 1 WHERE name = 'Widget' AND quantity > 0;

-- Step 2: Attempt a potentially failing operation
SAVEPOINT attempt_bonus;

UPDATE products SET price = price * 0.95 WHERE name = 'Widget';
-- Apply a 5% discount as a bonus

-- If this step failed (check in application code), roll back just this step
-- ROLLBACK TO SAVEPOINT attempt_bonus;

-- Step 3: Continue regardless of Step 2's outcome
UPDATE products SET in_stock = (quantity > 0) WHERE name = 'Widget';

COMMIT;

Summary of Commands

Try It Yourself

1. Start a transaction and make three separate updates to the products table, creating a savepoint after each one. Roll back to the middle savepoint and verify that the first update is preserved but the second and third are undone. Then commit.

2. Practice the error recovery pattern: start a transaction, create a savepoint, intentionally cause an error (e.g., invalid data type), roll back to the savepoint, then execute a correct statement and commit.

3. Create a transaction that updates prices for two different categories. Use savepoints so you can independently undo either category update without affecting the other.

4. Start a transaction with a savepoint. Make some changes, then RELEASE the savepoint. Verify that you can no longer ROLLBACK TO that savepoint but your changes are still uncommitted (until COMMIT).