Transactions in PostgreSQL

PostgreSQL's transaction behaviour differs from other databases in ways that affect migrations and error handling.

A few PostgreSQL behaviours to know beyond BEGIN/COMMIT:

Transactional DDL: CREATE TABLE, ALTER TABLE and most schema changes can be rolled back. A migration wrapped in a transaction either fully applies or leaves no trace — a big advantage over MySQL. Exceptions include CREATE INDEX CONCURRENTLY, VACUUM and CREATE DATABASE.
Errors abort the transaction: after any error inside a transaction, every further statement fails with "current transaction is aborted, commands ignored until end of transaction block" until you ROLLBACK.
Savepoints let you recover part-way: SAVEPOINT s1; ... ROLLBACK TO SAVEPOINT s1; undoes only the work after the savepoint and clears the error. Drivers use them for nested transactions. They have a cost, so don't create thousands in a loop.
The default isolation level is Read Committed; set others with BEGIN ISOLATION LEVEL REPEATABLE READ or SERIALIZABLE, and be ready to retry on SQLSTATE 40001.

Protect the server with timeouts, set per role or database:

statement_timeout cancels statements that run too long.
lock_timeout stops a statement from waiting forever for a lock.
idle_in_transaction_session_timeout kills sessions that opened a transaction and went quiet; they hold locks and block VACUUM.
transaction_timeout (PostgreSQL 17) caps a whole transaction.

Example

CREATE TABLE t (id int PRIMARY KEY);

BEGIN;
CREATE TABLE temp_report (x int);       -- DDL inside a transaction
INSERT INTO t VALUES (1);
SAVEPOINT before_risky;
INSERT INTO t VALUES (1);               -- ERROR: duplicate key
SELECT 1;                               -- ERROR: current transaction is aborted
ROLLBACK TO SAVEPOINT before_risky;     -- error cleared, row 1 still pending
INSERT INTO t VALUES (2);
ROLLBACK;                               -- undoes everything, even CREATE TABLE

SELECT to_regclass('temp_report');      -- NULL: the table was never created
SELECT count(*) FROM t;                 -- 0

ALTER ROLE CURRENT_USER SET idle_in_transaction_session_timeout = '60s';

Remember

Most DDL is transactional in PostgreSQL
After an error, ROLLBACK (or roll back to a savepoint) before continuing
Set statement, lock and idle-in-transaction timeouts

Watch out for

Catching an exception in app code and continuing to use the same, now-aborted transaction.

Check yourself

Inside BEGIN ... you run CREATE TABLE x and then ROLLBACK. What happens to x in PostgreSQL?

Official docs: www.postgresql.org/docs/current/sql-savepoint.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks