In PostgreSQL, what does transactional DDL let a schema migration do, and which commands refuse to run inside a transaction block?
answer
- catalogs are MVCC tables too
- ROLLBACK undoes ALTER TABLE
- CONCURRENTLY variants opt out
- SQLSTATE 25001 active_sql_transaction
- locks live until transaction end
basics
~20 sPostgreSQL runs almost all DDL inside a transaction, so a migration that fails halfway rolls back completely. A few commands refuse a transaction block: CREATE INDEX CONCURRENTLY, VACUUM, CREATE or DROP DATABASE, CREATE TABLESPACE and ALTER SYSTEM.
solid answer
~40 sIn PostgreSQL almost all DDL is transactional: `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE` and even `TRUNCATE` only write catalog rows under MVCC, so `BEGIN ... ROLLBACK` undoes them and a migration that fails on its third statement leaves no trace of the first two. The exceptions are commands that run their own internal transactions or touch files outside one database's catalogs: the `CONCURRENTLY` forms of `CREATE INDEX`, `REINDEX`, `DROP INDEX` and `DETACH PARTITION`, `VACUUM`, `CREATE`/`DROP DATABASE`, `CREATE`/`DROP TABLESPACE` and `ALTER SYSTEM`. Inside `BEGIN` they fail at once with `... cannot run inside a transaction block` (SQLSTATE `25001`). The price of the feature is that a table lock taken by DDL is normally held until the transaction ends, so a long migration transaction keeps `ACCESS EXCLUSIVE` on `accounts` for its whole duration.
code
sql · 11 linesBEGIN;
ALTER TABLE accounts ADD COLUMN bio text;
CREATE TABLE follows (
follower_id bigint NOT NULL REFERENCES accounts (id),
followee_id bigint NOT NULL REFERENCES accounts (id),
PRIMARY KEY (follower_id, followee_id)
);
CREATE INDEX CONCURRENTLY follows_followee_idx ON follows (followee_id);
-- ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
ROLLBACK;
-- accounts has no bio column and follows does not existgo deeper
Recall that PostgreSQL can roll back CREATE, ALTER and DROP, and name a few commands that refuse a transaction block, starting with CREATE INDEX CONCURRENTLY and VACUUM.
Explain why it works: catalogs are MVCC tables, so DDL is catalog writes by a transaction. Name the 25001 error and why the CONCURRENTLY forms need their own transactions.
Show the cost side: locks are held to transaction end, so you split releases into short transactions with one strong lock each and keep backfills out of the DDL transaction.
Weigh atomic releases against lock duration across a fleet of migrations, and decide which steps a team's runner must allow to run outside a transaction.
## What transactional DDL means in PostgreSQL In PostgreSQL the definition of every table lives in ordinary **system catalogs** such as `pg_class`, `pg_attribute` and `pg_constraint`. Those catalogs are tables with MVCC row versions like any other, so a `CREATE TABLE`, `ALTER TABLE` or `DROP TABLE` is, underneath, a set of catalog inserts, updates and deletes made by the current transaction. No other session sees the new definition until that transaction commits, and if it rolls back, the catalog rows it wrote never become visible. **Transactional DDL** is the name for this property: schema changes obey `BEGIN`, `COMMIT`, `ROLLBACK` and `SAVEPOINT` exactly as data changes do. ## What it buys a migration 1. **All-or-nothing releases.** A migration that adds a column to `accounts`, creates a `follows` table and adds a constraint either lands completely or not at all. A failure on the last step leaves no orphan column from the first. 2. **Rehearsal.** In psql you can run `BEGIN;`, the `ALTER TABLE`, inspect the result with `\d accounts`, and `ROLLBACK;` without ever changing production. 3. **Savepoints.** A step can run under `SAVEPOINT`; if it errors, `ROLLBACK TO SAVEPOINT` recovers and the rest of the migration continues. 4. **Even `TRUNCATE` rolls back.** It is transactional too, although, like the table-rewriting forms of `ALTER TABLE`, it is not MVCC-safe: a concurrent transaction using a snapshot taken before it committed sees the table as empty. ## Commands that refuse a transaction block A handful of commands call an internal check and error out when a transaction block is open. They fail with `ERROR: <command> cannot run inside a transaction block` and SQLSTATE `25001` (`active_sql_transaction`). | Command | Why it cannot be wrapped | |---|---| | `CREATE INDEX CONCURRENTLY`, `REINDEX CONCURRENTLY`, `DROP INDEX CONCURRENTLY` | Commits several internal transactions so that writes keep flowing during the build or drop | | `ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY` | Runs as two transactions | | `VACUUM` | Manages its own transactions, table by table | | `CREATE DATABASE`, `DROP DATABASE` | Copies or removes whole directories, which cannot be rolled back | | `CREATE TABLESPACE`, `DROP TABLESPACE` | Creates or removes a directory on disk | | `ALTER SYSTEM` | Writes `postgresql.auto.conf`, a file outside the catalogs | A plain `CREATE INDEX` or `REINDEX` is fine inside a transaction; only the concurrent variants refuse. The same error appears when psql runs a script with `-1` (`--single-transaction`), because that option wraps the whole script in `BEGIN` and `COMMIT`. A migration runner that wraps every migration in a transaction therefore needs a way to run such a step on its own. ## The price: locks are held to the end of the transaction A table lock taken by a DDL statement is normally held until the transaction ends, not until the statement finishes. Rolling back to a savepoint releases the locks taken after it, but nothing else does. In practice: - An `ALTER TABLE accounts ADD COLUMN` followed by a ten-minute backfill `UPDATE` in the same transaction keeps `ACCESS EXCLUSIVE` on `accounts` for all ten minutes, and that mode blocks even plain `SELECT`. - Several DDL statements on different tables accumulate locks, which raises the chance of a deadlock with application transactions that touch those tables in another order; the deadlock detector runs after `deadlock_timeout` (1 s by default) and aborts one side. - The longer the migration transaction, the longer every session queued behind its locks waits. So the safe shape for a busy database is the opposite of one big transaction: short transactions, at most one strong lock in each, and a `lock_timeout` on each. ## Edges worth knowing - `ALTER TYPE ... ADD VALUE` on an enum may run inside a transaction block, but the new label cannot be used until that transaction commits. - Rollback undoes catalog changes, not time. A rewrite that ran for twenty minutes and then rolled back still cost twenty minutes of `ACCESS EXCLUSIVE`. - Engines whose DDL commits implicitly, MySQL being the usual example, cannot roll back a half-applied migration; PostgreSQL can, and interviewers often ask for exactly that contrast. ## How to use it well - Group statements that must land together, such as a new table and the constraint that references it, into one transaction. - Keep data backfills out of the transaction that holds the `ACCESS EXCLUSIVE` lock. - Run the `CONCURRENTLY` commands as separate, non-transactional steps, and check afterwards that they finished.
- Why might you deliberately not wrap a multi-step PostgreSQL migration in one transaction?Because locks accumulate until the transaction ends. An `ALTER TABLE accounts` followed by a backfill in the same transaction holds `ACCESS EXCLUSIVE`, blocking reads, for the whole backfill. Several DDL statements also stack locks on several tables, which invites deadlocks with live traffic. Splitting the release into short transactions, each with one strong lock and a `lock_timeout`, trades atomicity for availability.
- A migration file contains ALTER TABLE accounts ADD COLUMN and CREATE INDEX CONCURRENTLY; how do you run it in PostgreSQL?Split it. The `ALTER TABLE` can run in a normal short transaction; the concurrent index build must run outside any transaction block, so it goes in its own step that the runner executes without `BEGIN`. If the build fails it can leave an invalid index behind, which you must check for and drop before retrying.
- PostgreSQL DDL commits implicitly, so a failed migration stays half-applied.
- CREATE INDEX CONCURRENTLY can sit inside BEGIN with the rest of the migration.
- ALTER TABLE releases its lock when the statement ends, even inside BEGIN.
- TRUNCATE cannot be rolled back in PostgreSQL.
- Wrapping the whole release, backfill included, in one transaction is always safest.