cubic
Try cubic for free

Set lock_timeout Before Blocking DDL in PostgreSQL

Flags PostgreSQL ALTER TABLE and other blocking DDL run without lock_timeout, which can stall every query on the table while it waits for its lock.

Catch this in your code

What it flags

Flaggedmigrations/20260915_orders_priority.sql
BEGIN;

-- Waits behind open transactions on orders while new queries queue behind it
ALTER TABLE orders
  ADD COLUMN priority smallint NOT NULL DEFAULT 0;

ALTER TABLE orders
  ALTER COLUMN status SET DEFAULT 'pending';

COMMIT;
Passesmigrations/20260915_orders_priority.sql
BEGIN;

-- Give up after 3s instead of stalling the queries queued behind the lock
SET LOCAL lock_timeout = '3s';

ALTER TABLE orders
  ADD COLUMN priority smallint NOT NULL DEFAULT 0;

ALTER TABLE orders
  ALTER COLUMN status SET DEFAULT 'pending';

COMMIT;

Why it matters

An ALTER TABLE that needs an ACCESS EXCLUSIVE lock must wait for every transaction already using the table, including a slow report or a session left idle in a transaction. While it waits, every new query on that table queues behind it, so a statement that would finish in milliseconds can stop all traffic to the table and exhaust the connection pool. With lock_timeout set to a few seconds, the migration fails fast, the queued queries proceed, and the deploy can retry later. Staging rarely has a long transaction holding the table, so the problem first appears during a production deploy.

Agent instructions

cubic checks every pull request against these instructions. Paste them into AGENTS.md or Cursor rules and your AI coding tool follows them while it writes code.

AGENTS.md

Add this section to AGENTS.md at the repository root. Codex, Cursor, and other AI coding tools read it.

## Set lock_timeout Before Blocking DDL in PostgreSQL

Applies to `**/*.sql`, `**/migrations/**`, `**/migrate/**`.

Review PostgreSQL migrations for DDL that can wait in the lock queue and stall all traffic to a table.

Flag changes that:
- Run `ALTER TABLE` on an existing table without setting `lock_timeout` earlier in the same session or transaction. Most forms, including ADD COLUMN, DROP COLUMN, ALTER COLUMN TYPE, SET DEFAULT, SET NOT NULL, ADD CHECK, and RENAME, take an ACCESS EXCLUSIVE lock.
- Run other lock-heavy DDL on live tables without `lock_timeout`: ADD FOREIGN KEY (which also locks the referenced table), `CREATE TRIGGER`, `TRUNCATE`, `DROP TABLE`, or `LOCK TABLE`.
- Weaken the limit: `SET LOCAL lock_timeout` outside a transaction block (it has no effect there), `lock_timeout = 0` (no limit), or a value in minutes.
- Hold an ACCESS EXCLUSIVE lock across slow work in the same transaction, such as a backfill, a full-table scan, or DDL on several busy tables in a row.
- Retry a lock timeout in a loop with no attempt limit or delay between attempts.

Do not flag:
- DDL on tables created earlier in the same migration.
- Migrations where the runner configuration, the database role settings, or a shared migration helper already sets `lock_timeout`.
- `CREATE INDEX CONCURRENTLY`, `DROP INDEX CONCURRENTLY`, and `ALTER TABLE ... VALIDATE CONSTRAINT`, whose locks do not conflict with reads or writes.

Checks files matching **/*.sql, **/migrations/**, **/migrate/**.

More PostgreSQL agents

See all PostgreSQL code review standards

Run this agent on every pull request

cubic reviews every pull request against the agents you turn on and comments on the lines that break them.

Catch this in your code