CodePark/ questions / Transactions & ACID
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Transactions & ACID

In PostgreSQL, these statements are sent one at a time on one connection. The second insert hits a unique violation. What ends up saved?

BEGIN;
INSERT INTO users (email) VALUES ('a@x.com');
INSERT INTO users (email) VALUES ('a@x.com');  -- unique violation
INSERT INTO audit (msg) VALUES ('done');
COMMIT;
Nothing: the transaction is aborted, the audit insert fails with current transaction is aborted, and COMMIT performs a rollback

Nothing is saved. After any error, PostgreSQL marks the transaction as aborted. It then rejects every further command until the transaction ends, so the audit insert fails too. COMMIT on an aborted transaction performs a rollback.

"The first user and the audit row; the duplicate is skipped" is wrong, because PostgreSQL does not skip a failed statement and carry on. To recover from an expected error and continue, use a SAVEPOINT and ROLLBACK TO SAVEPOINT.