Why does CREATE INDEX CONCURRENTLY fail inside a transaction?

Non-transactional statement mixed into a multi-statement migration

Critical: this fails outright or takes production down.

What happens

Statements like CREATE INDEX CONCURRENTLY refuse to run in a transaction block. Mixed with other DDL in one migration, either the whole file fails, or the runner drops the transaction and a mid-file error strands the schema between states with no rollback.

Why it is dangerous on a populated table

On an empty table this finishes before anyone notices. On a populated one the lock is held for the whole operation, every query on the table queues behind it, and the connection pool fills from the back.

Fires on

ALTER TABLE orders ADD COLUMN region text;
CREATE INDEX CONCURRENTLY idx ON orders (region);

The safe pattern

-- migration 1 (transactional):
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region text;
-- migration 2 (alone, outside a transaction):
--    CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

-- Guarded transactional DDL mixed with CONCURRENTLY: still a mix.
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region text;
CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

ALTER TABLE orders ADD COLUMN region text;
CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

-- The recommended pattern: a lock_timeout guard (BV034) and only
-- non-transactional statements, in a migration run outside a transaction.
SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

ALTER TABLE orders ADD COLUMN region text;
ALTER TABLE orders ADD COLUMN city text;

How to check locally

npx bolvrk check migration.sql