Transactions and SAVEPOINT in PostgreSQL

A transaction groups one or more SQL statements into a single all-or-nothing unit of work. PostgreSQL guarantees full ACID compliance: either every statement in the transaction succeeds and is made permanent with COMMIT, or none of them take effect, undone entirely with ROLLBACK. This is essential whenever multiple related changes must succeed or fail together — such as debiting one account and crediting another.

PostgreSQL also has a distinctive behavior worth knowing: once any statement inside a transaction produces an error, the entire transaction is marked as aborted, and every subsequent statement is rejected until you issue a ROLLBACK — even statements that would otherwise have succeeded.

Why it matters

PostgreSQL is stricter than some databases: after any error inside a transaction it refuses every further statement until you roll back. That protects your data and surprises newcomers. Savepoints are the tool for recovering from an expected error without throwing the whole transaction away.

BEGIN, COMMIT, and ROLLBACK

A transaction starts with BEGIN; (or START TRANSACTION;), after which every statement is provisional. COMMIT; makes all of those changes permanent and visible to other connections. ROLLBACK; discards every change made since BEGIN, as if none of it had happened. Outside of an explicit BEGIN, PostgreSQL runs every individual statement in its own implicit transaction, which is why a single INSERT without BEGIN is already atomic on its own.

The "Aborted Transaction" Behavior

If any statement inside a BEGIN block raises an error, PostgreSQL puts the whole transaction into an aborted state. You will see the error current transaction is aborted, commands ignored until end of transaction block for every further statement, even valid ones, until you run ROLLBACK. This surprises developers coming from databases with more forgiving error handling, and it is precisely why SAVEPOINT exists.

SAVEPOINT: Partial Rollback

SAVEPOINT name; marks a point inside a transaction that you can roll back to without discarding the entire transaction. ROLLBACK TO SAVEPOINT name; undoes everything since that savepoint (and clears the aborted state, if the error happened after it) while keeping earlier work in the transaction intact. RELEASE SAVEPOINT name; discards the savepoint once you no longer need to roll back to it. This is especially useful for handling an expected failure in one step of a larger transaction — such as attempting an INSERT that might violate a unique constraint — without losing everything else already done in that transaction.

Examples

BEGIN;

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

COMMIT;

BEGIN
UPDATE 1
UPDATE 1
COMMIT

BEGIN;

INSERT INTO categories (name) VALUES ('Books');

SAVEPOINT before_duplicate;
INSERT INTO categories (name) VALUES ('Laptops'); -- violates UNIQUE, fails
ROLLBACK TO SAVEPOINT before_duplicate;

INSERT INTO categories (name) VALUES ('Stationery');

COMMIT;

BEGIN
INSERT 0 1
SAVEPOINT
ERROR:  duplicate key value violates unique constraint "categories_name_key"
ROLLBACK
INSERT 0 1
COMMIT

Exercise

Inside one transaction, insert product 1. Then set a savepoint and try to insert a second row that reuses id 1, which fails. Roll back to the savepoint, insert product 2 instead, and commit. Count the rows.

Expected output: total then 2

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

BEGIN;
INSERT INTO products (id, name) VALUES (1, 'Pen');

-- TODO: create a savepoint named before_second

INSERT INTO products (id, name) VALUES (1, 'Duplicate');

-- TODO: roll back to the savepoint

INSERT INTO products (id, name) VALUES (2, 'Bag');
COMMIT;

SELECT COUNT(*) AS total FROM products;

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

BEGIN;
INSERT INTO products (id, name) VALUES (1, 'Pen');

SAVEPOINT before_second;

INSERT INTO products (id, name) VALUES (1, 'Duplicate');

ROLLBACK TO SAVEPOINT before_second;

INSERT INTO products (id, name) VALUES (2, 'Bag');
COMMIT;

SELECT COUNT(*) AS total FROM products;

Quick quiz

Interview questions