After a failed statement, how does ROLLBACK TO SAVEPOINT let the transaction continue?

## The problem the pattern solves You are inside an explicit transaction, several statements in, and one statement raises an error — a unique violation, a check-constraint failure, a bad cast. What can the session do next? That depends on the engine, and this is one of the few places where the difference is loud enough that a portable answer has to name it: - Some engines perform **statement-level rollback**: the failing statement's own partial effects are undone, the transaction stays usable, and the application decides what to do next. - PostgreSQL aborts the **whole transaction**. Every subsequent statement is rejected with `current transaction is aborted, commands ignored until end of transaction block`. The only accepted moves are ending the transaction — or rewinding to a savepoint. Either way, the language-level tool that makes error recovery explicit and portable is the savepoint. ## The pattern ```sql BEGIN; INSERT INTO import_run (id, started_at) VALUES (17, CURRENT_TIMESTAMP); SAVEPOINT row_sp; INSERT INTO customers (id, email) VALUES (7, '[email protected]'); -- on error: ROLLBACK TO SAVEPOINT row_sp; INSERT INTO import_error (run_id, row_id, reason) VALUES (17, 7, 'duplicate email'); -- on success instead: -- RELEASE SAVEPOINT row_sp; SAVEPOINT row_sp2; INSERT INTO customers (id, email) VALUES (8, '[email protected]'); RELEASE SAVEPOINT row_sp2; COMMIT; ``` The savepoint is set **before** the statement that may fail — after the failure it is too late, and on an engine that has aborted the transaction you cannot even execute `SAVEPOINT` any more. `ROLLBACK TO SAVEPOINT` in the error path does two jobs at once: it undoes whatever the failed statement managed to change, and it clears the transaction's failed state so that ordinary statements are accepted again. Note that the error-logging `INSERT` above runs *after* the rewind — it would be rejected if issued while the transaction was still in the aborted state. ## What the rewind does not do It does not re-run the failed statement, and it does not turn the error into a success. Your code still has to decide: skip the row, substitute a value, log it, or give up and roll the whole transaction back. The savepoint only preserves the option of continuing. It also does not commit anything. The pre-savepoint work is still pending; if the loop later hits a fatal condition and issues a plain `ROLLBACK`, all of it goes. Partial rollback protects work from *this statement's* failure, not from the transaction's overall fate. ## Success path: release If every iteration sets a savepoint and none of them ever releases, a long batch ends up carrying one live savepoint per row. `RELEASE SAVEPOINT` on the success path keeps exactly one mark live at a time. Distinct names per iteration are safer than reusing one name, because engines disagree about what re-establishing an existing name means. ## Cost, briefly A savepoint per statement is not free — each one is bookkeeping the engine carries for the life of the transaction, and wrapping every row of a large import in its own savepoint measurably slows the import. The judgment call is granularity: a savepoint per row buys row-level recovery at row-level cost, a savepoint per chunk of rows loses the whole chunk when any row in it fails but costs far less. Choose per how likely failures are and how expensive re-doing a chunk would be. ## Portability notes The statements themselves are widely supported, though spellings vary (`ROLLBACK TO SAVEPOINT name`, with `WORK` and `SAVEPOINT` optional in some dialects; T-SQL spells the feature `SAVE TRANSACTION` / `ROLLBACK TRANSACTION name`). What is *not* portable is the assumption about what a statement error does to the transaction — so write the savepoint even against an engine that would have let you continue anyway, and the code behaves the same everywhere. ## What interviewers listen for The strong answer names the divergence in error behaviour, places the `SAVEPOINT` before the risky statement rather than after the failure, notes that the rewind restores a usable transaction, and mentions releasing on success plus the granularity tradeoff. The weak answer assumes errors always just skip, or reaches for `COMMIT` in the error handler and expects partial success.