Can VALIDATE CONSTRAINT run in the same migration as exclusive-lock DDL?
VALIDATE CONSTRAINT mixed with exclusive-lock DDL
Note: this works and blocks nothing, but a performance regression is likely.
What happens
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.
Why it is dangerous on a populated table
Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.
Fires on
ALTER TABLE orders ADD COLUMN region text;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;The safe pattern
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;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.
Fires (1)
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region text;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;Stays silent (1)
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;How to check locally
This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:
npx bolvrk check migration.sql --remote