Long transaction blocks a DDL migration

Long transaction blocks a DDL migration
Type: incident simulation · Topic: Locks & Transactions · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-02/06-long-transaction-blocks-ddl

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a migration's `ALTER TABLE` needs an ACCESS EXCLUSIVE lock, but an
old transaction was still open holding an ACCESS SHARE lock on the table (from
an earlier query it never committed). The DDL couldn't get its lock and waited —
it looked "stuck", but it was blocked by the long transaction.

How it was found: pg_stat_activity showed the ALTER active with
wait_event_type = 'Lock', and an old session with a large now() - xact_start;
pg_blocking_pids tied them together.

The fix: SELECT pg_terminate_backend(<long_tx_pid>);
Terminating the long transaction releases its lock, the ALTER acquires ACCESS
EXCLUSIVE, and the schema change completes.

Lesson: blocked DDL is usually a *who-holds-the-lock* problem. Find the oldest
transaction (xact_start) blocking it and end that one — don't kill the DDL, and
don't reach for indexes/ANALYZE. Prevent it with short transactions and a
`lock_timeout` on migrations.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The ALTER TABLE didn't 'hang on its own' — it's waiting on a lock. Look at pg_stat_activity: the DDL is active with wait_event_type = 'Lock'.
2. Find what holds the lock: an old transaction with a large now() - xact_start. The DDL needs ACCESS EXCLUSIVE and can't get it while that transaction is open.
3. Terminate the old long-running transaction (the blocker), not the DDL. Once it's gone, the ALTER acquires its lock and completes.