Savepoints and the aborted-transaction state in PostgreSQL

What it is

A transaction block (BEGIN ... COMMIT) is atomic: all of its changes are kept or none. The PostgreSQL tutorial states that after an error the block is put in an aborted state, and that ROLLBACK TO a savepoint is the only way to regain control short of rolling the whole transaction back. Commands issued in that state fail with SQLSTATE 25P02, in_failed_sql_transaction in the error-code appendix, whose message reads "current transaction is aborted, commands ignored until end of transaction block". SAVEPOINT name marks a point; ROLLBACK TO SAVEPOINT name discards the changes made after it and keeps the transaction open; RELEASE SAVEPOINT name drops the mark and keeps the changes. Savepoints can be nested.

Why it matters

Application code that catches a database exception and keeps issuing statements on the same connection fails on every one of them, often with the confusing 25P02 error far from the original cause. Batch jobs that want "skip the bad row, keep the rest" need savepoints or per-row transactions. Connection pools that hand back a connection in the aborted state poison the next user of that connection.

How to apply

• Set a savepoint immediately before a statement that may legitimately fail (a constraint violation the code intends to handle), roll back to it in the exception handler, then continue; release it on success.
• For row-by-row imports, either wrap each row in a savepoint and roll back only the failing ones, or remove the error source instead: validate in a staging table first, or use INSERT ... ON CONFLICT DO NOTHING.
• In PL/pgSQL, a BEGIN ... EXCEPTION ... END block traps errors that would otherwise abort the function and the surrounding transaction; the documentation notes that such a block is significantly more expensive to enter and exit than one without, so wrap only the statement that needs it.
• Keep transactions short: a long transaction with many savepoints holds its locks and keeps old row versions alive for its whole duration.
• Check what the driver does: whether it opens transactions implicitly, and whether it reports 25P02 distinctly so that the handler rolls back rather than retrying the statement.

Pitfalls

ROLLBACK TO does not end the transaction; changes before the savepoint remain uncommitted and invisible to other sessions until COMMIT. Some drivers and ORMs can wrap every statement in a savepoint; each savepoint opens a subtransaction with its own bookkeeping, so keep that mode out of hot paths. The tutorial notes that releasing or rolling back to a savepoint releases every savepoint defined after it, and that a savepoint stays defined after a rollback to it, so it can be reused.