SET NOT NULL scanning the table under ACCESS EXCLUSIVE

Warning — this works, but blocks traffic or rewrites data at scale.

What it catches

SET NOT NULL must verify every existing row, and it does so while holding ACCESS EXCLUSIVE — the table is fully unavailable for the scan. The safe path is a CHECK (col IS NOT NULL) NOT VALID constraint, VALIDATE CONSTRAINT (which does not block), then SET NOT NULL, which sees the validated constraint and skips the scan.

Fires on

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

Do this instead

Add CHECK (col IS NOT NULL) NOT VALID (instant), VALIDATE it in a later migration (non-blocking scan), then SET NOT NULL — on Postgres 12+ it sees the validated constraint and skips its own scan, making the ACCESS EXCLUSIVE window a metadata flip. Drop the helper constraint afterwards.

SET lock_timeout = '5s';
ALTER TABLE orders ADD CONSTRAINT orders_region_nn
  CHECK (region IS NOT NULL) NOT VALID;
-- later migrations:
--    ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn;  -- non-blocking
--    ALTER TABLE orders ALTER COLUMN region SET NOT NULL;      -- scan skipped (PG12+)
--    ALTER TABLE orders DROP CONSTRAINT orders_region_nn;

Catch this before it ships

This rule runs locally in the free CLI — or with the full corpus through the hosted service: npx bolvrk check migration.sql

Browse all 69 rules

← BV010 BV012 →