note
hosted, paid — --remote
VALIDATE CONSTRAINT mixed with exclusive-lock DDL
Note — this works and blocks nothing, but a performance regression is likely.
What it catches
VALIDATE CONSTRAINT deliberately takes only SHARE UPDATE EXCLUSIVE so it can run without blocking — but in the same transaction as ACCESS EXCLUSIVE DDL, the exclusive locks are held while the validation scans the whole table. The non-blocking scan drags the blocking locks out with it.
Fires on
ALTER TABLE orders ADD COLUMN region text;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;Do this instead
Keep VALIDATE CONSTRAINT in its own migration (its own transaction). Alone, it holds only SHARE UPDATE EXCLUSIVE for the scan; in a transaction with exclusive-lock DDL, those exclusive locks are held until commit — for the whole scan.
-- migration 1: the exclusive-lock DDL, brief and guarded
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region text;
-- migration 2 (own transaction): the non-blocking scan
-- ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;Catch this before it ships
This rule runs in the hosted service on Startup and above — add --remote with a team token, or use the GitHub Action: npx bolvrk check migration.sql