SDB502[high]Data-dependent

SET NOT NULL on an existing column

SET NOT NULL scans the table under an ACCESS EXCLUSIVE lock and fails on any NULL.

Why it's risky in PostgreSQL

PostgreSQL checks every row while blocking reads and writes. If a single NULL exists, the migration fails.

On PostgreSQL 12+, add a CHECK (col IS NOT NULL) NOT VALID, validate it, then SET NOT NULL skips the scan.

What triggers it, and the safer way

unsafe.sql

ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;

safer.sql

ALTER TABLE orders ADD CONSTRAINT customer_id_nn CHECK (customer_id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT customer_id_nn;
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT customer_id_nn;

Suppress it when you're sure

Put this comment on the line before the statement. The reason is required and shows in the report.

suppression

-- shadowdb:allow SDB502 <why this is safe here>

Applies to PostgreSQL 14, 15, 16, 17.