ROLLBACK undoes the whole transaction. Sometimes you want to undo only the last part.
A savepoint is a named mark inside a transaction:
BEGIN;startUPDATE … ;keptSAVEPOINT before_fee;set a markUPDATE … ;undoneROLLBACK TO SAVEPOINT before_fee;go back to the markCOMMIT;keep the rest
| Statement | What it does |
|---|---|
SAVEPOINT name; | Sets a mark with this name. |
ROLLBACK TO SAVEPOINT name; | Undoes every change after the mark. The transaction stays open. The mark stays too. |
RELEASE SAVEPOINT name; | Removes the mark. The changes stay. |
Continue after an error
In PostgreSQL, an error aborts the whole transaction (Lesson 3). ROLLBACK TO SAVEPOINT also ends the aborted state, if the mark is before the error:
| Step | Statement | Result |
|---|---|---|
| 1 | BEGIN; | |
| 2 | add 50 to Ava | works |
| 3 | SAVEPOINT before_fee; | |
| 4 | take 100 from Sofia | fails: transaction aborted |
| 5 | ROLLBACK TO SAVEPOINT before_fee; | not aborted any more; Ava's 50 is still there |
| 6 | add 30 to Liam | works |
| 7 | COMMIT; | Ava and Liam keep their changes |
A batch job can use this: set a savepoint before each risky step, and skip only the steps that fail.