I'm following this answer to add a NOT NULL constraint to an existing DB table with ~100M rows. However, when I try running the backend process takes out a ShareLock.
ALTER TABLE mytable VALIDATE CONSTRAINT myfield_not_null;
I confirmed this by checking pg_locks in another session, where I can see the following (5447 is the pid of the backend attempting the VALIDATE CONSTRAINT):
mydb=> select l.pid, l.mode, l.granted, l.waitstart, a.xact_start, a.query_start, a.state from pg_locks l join pg_stat_activity a on l.pid = a.pid;
pid | mode | granted | waitstart | xact_start | query_start | state
-------+--------------------------+---------+-------------------------------+-------------------------------+-------------------------------+--------
5447 | ShareLock | t | | 2023-10-06 14:58:23.133136+00 | 2023-10-06 14:58:23.355743+00 | active
5447 | ShareUpdateExclusiveLock | t | | 2023-10-06 14:58:23.133136+00 | 2023-10-06 14:58:23.355743+00 | active
The docs explicitly say:
a VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint. The validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered.
The ShareLock is blocking some queries that need RowExclusiveLock (update/insert/delete).
How can I validate the constraint without taking a ShareLock?
join pg_class c on l.relation = c.oidc.relnamewas indeed mytable. The only "weird" things are some row triggers, two custom statistics, a different already validated constraint, and a handful of outbound and inbound FKs