On 7th of April 2025, Álvaro Herrera committed patch:
Allow NOT NULL constraints to be added as NOT VALID This allows them to be added without scanning the table, and validating them afterwards without holding access exclusive lock on the table after any violating rows have been deleted or fixed. Doing ALTER TABLE ... SET NOT NULL for a column that has an invalid not-null constraint validates that constraint. ALTER TABLE .. VALIDATE CONSTRAINT is also supported. There are various checks on whether an invalid constraint is allowed in a child table when the parent table has a valid constraint; this should match what we do for enforced/not enforced constraints. pg_attribute.attnotnull is now only an indicator for whether a not-null constraint exists for the column; whether it's valid or invalid must be queried in pg_constraint. Applications can continue to query pg_attribute.attnotnull as before, but now it's possible that NULL rows are present in the column even when that's set to true. For backend internal purposes, we cache the nullability status in CompactAttribute->attnullability that each tuple descriptor carries (replacing CompactAttribute.attnotnull, which was a mirror of Form_pg_attribute.attnotnull). During the initial tuple descriptor creation, based on the pg_attribute scan, we set this to UNRESTRICTED if pg_attribute.attnotnull is false, or to UNKNOWN if it's true; then we update the latter to VALID or INVALID depending on the pg_constraint scan. This flag is also copied when tupledescs are copied. Comparing tuple descs for equality must also compare the CompactAttribute.attnullability flag and return false in case of a mismatch. pg_dump deals with these constraints by storing the OIDs of invalid not-null constraints in a separate array, and running a query to obtain their properties. The regular table creation SQL omits them entirely. They are then dealt with in the same way as "separate" CHECK constraints, and dumped after the data has been loaded. Because no additional pg_dump infrastructure was required, we don't bump its version number. I decided not to bump catversion either, because the old catalog state works perfectly in the new world. (Trying to run with new catalog state and the old server version would likely run into issues, however.) System catalogs do not support invalid not-null constraints (because commit 14e87ffa5c54 didn't allow them to have pg_constraint rows anyway.) Author: Rushabh Lathia <rushabh.lathia@gmail.com> Author: Jian He <jian.universality@gmail.com> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Tested-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Discussion: https://postgr.es/m/CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com
We got another ALTER TABLE that can be done with less long-term locking. YAY!
When you run ALTER TABLE (usually), you obtain ACCESS EXCLUSIVE lock on the table, prohibiting anything else from touching the table for the duration of lock. And then alter table does it's thing. Sometimes this process takes long time, which means that for long time nothing can even select from the table.
Luckily we can add things in invalid form, and then just validate them. The difference is that while we still need ACCESS EXCLUSIVE lock for adding the constraint, this process is very fast if it's being done as invalid. And validation doesn't require as heavy lock.
Let's see how it works. I made two identical tables, with simplistic schema, no constraints, and a bit of data:
=$ create table copy_1 as select * from plans; SELECT 1799020 =$ create table copy_2 as select * from plans; SELECT 1799020 =$ \d copy_1 Table "public.copy_1" Column │ Type │ Collation │ Nullable │ Default ──────────────────┼──────────────────────────┼───────────┼──────────┼───────── id │ text │ │ │ plan │ text │ │ │ entered_on │ timestamp with time zone │ │ │ is_public │ boolean │ │ │ is_anonymized │ boolean │ │ │ title │ text │ │ │ delete_key │ text │ │ │ is_deleted │ boolean │ │ │ added_by │ text │ │ │ optimization_for │ text │ │ │ query │ text │ │ │ comments │ text │ │ │ =$ select count(*), pg_size_pretty( pg_table_size('copy_1'::regclass)) from copy_1; count │ pg_size_pretty ─────────┼──────────────── 1799020 │ 7079 MB (1 row)
Now, let's see the locks, and durations:
=$ begin; BEGIN Time: 0.135 ms =$ alter table public.copy_1 alter column id set not null; ALTER TABLE Time: 205.890 ms =$ select * from pg_locks where pid = pg_backend_pid(); locktype │ database │ relation │ page │ tuple │ virtualxid │ transactionid │ classid │ objid │ objsubid │ virtualtransaction │ pid │ mode │ granted │ fastpath │ waitstart ───────────────┼──────────┼──────────┼────────┼────────┼────────────┼───────────────┼─────────┼────────┼──────────┼────────────────────┼────────┼─────────────────────┼─────────┼──────────┼─────────── relation │ 16403 │ 12073 │ [null] │ [null] │ [null] │ [null] │ [null] │ [null] │ [null] │ 69/2 │ 494545 │ AccessShareLock │ t │ t │ [null] virtualxid │ [null] │ [null] │ [null] │ [null] │ 69/2 │ [null] │ [null] │ [null] │ [null] │ 69/2 │ 494545 │ ExclusiveLock │ t │ t │ [null] relation │ 16403 │ 579842 │ [null] │ [null] │ [null] │ [null] │ [null] │ [null] │ [null] │ 69/2 │ 494545 │ AccessExclusiveLock │ t │ f │ [null] transactionid │ [null] │ [null] │ [null] │ [null] │ [null] │ 1788 │ [null] │ [null] │ [null] │ 69/2 │ 494545 │ ExclusiveLock │ t │ f │ [null] (4 rows) Time: 0.924 ms =$ commit; COMMIT
So, we can see that alter table took 200ms, as Pg had to scan all the rows in the table to check whether there are any rows with id being NULL.
And in the pg_locks data we see (lock #3) that there is AccessExclusiveLock on relation = 579842, which happens to be copy_1:
=$ select 579842::regclass; regclass ────────── copy_1 (1 row)
Now, let's try to do the same, to the other table. This has to be done using slightly different syntax, as you can't just add NOT VALID to previously used alter table:
=$ alter table public.copy_2 alter column id set not null not valid; ERROR: syntax error at or near "not" LINE 1: ... table public.copy_2 alter column id set not null not valid; ^
New syntax will use named constraint:
=$ begin; BEGIN Time: 0.176 ms =$ alter table public.copy_2 add constraint id_not_null not null id not valid; ALTER TABLE Time: 0.857 ms =$ select * from pg_locks where pid = pg_backend_pid() and locktype = 'relation' and relation = 'copy_2'::regclass; locktype │ database │ relation │ page │ tuple │ virtualxid │ transactionid │ classid │ objid │ objsubid │ virtualtransaction │ pid │ mode │ granted │ fastpath │ waitstart ──────────┼──────────┼──────────┼────────┼────────┼────────────┼───────────────┼─────────┼────────┼──────────┼────────────────────┼────────┼─────────────────────┼─────────┼──────────┼─────────── relation │ 16403 │ 1134710 │ [null] │ [null] │ [null] │ [null] │ [null] │ [null] │ [null] │ 72/2 │ 497708 │ AccessExclusiveLock │ t │ f │ [null] (1 row) Time: 0.992 ms =$ \d copy_2 Table "public.copy_2" Column │ Type │ Collation │ Nullable │ Default ──────────────────┼──────────────────────────┼───────────┼──────────┼───────── id │ text │ │ not null │ plan │ text │ │ │ entered_on │ timestamp with time zone │ │ │ is_public │ boolean │ │ │ is_anonymized │ boolean │ │ │ title │ text │ │ │ delete_key │ text │ │ │ is_deleted │ boolean │ │ │ added_by │ text │ │ │ optimization_for │ text │ │ │ query │ text │ │ │ comments │ text │ │ │ =$ commit; COMMIT
Please note that the alter table took now 0.857ms. And immediately afterwards we see that id can't be null (in \d output).
It doesn't show us that the constraint isn't valid, which is unfortunate, but it already disallows creation of new rows with id being NULL:
$ insert into copy_2 (id, plan) values (NULL, 'test'); ERROR: null value in column "id" of relation "copy_2" violates not-null constraint DETAIL: Failing row contains (null, test, null, null, null, null, null, null, null, null, null, null).
Anyway, we can now also validate this constraint:
=$ begin; BEGIN Time: 0.120 ms alter table public.copy_2 validate constraint id_not_null; =$ ALTER TABLE Time: 191.828 ms =$ select * from pg_locks where pid = pg_backend_pid() and locktype = 'relation' and relation = 'copy_2'::regclass; locktype │ database │ relation │ page │ tuple │ virtualxid │ transactionid │ classid │ objid │ objsubid │ virtualtransaction │ pid │ mode │ granted │ fastpath │ waitstart ──────────┼──────────┼──────────┼────────┼────────┼────────────┼───────────────┼─────────┼────────┼──────────┼────────────────────┼────────┼──────────────────────────┼─────────┼──────────┼─────────── relation │ 16403 │ 1134710 │ [null] │ [null] │ [null] │ [null] │ [null] │ [null] │ [null] │ 72/3 │ 497708 │ ShareUpdateExclusiveLock │ t │ f │ [null] (1 row) Time: 0.511 ms =$ \d copy_2 Table "public.copy_2" Column │ Type │ Collation │ Nullable │ Default ──────────────────┼──────────────────────────┼───────────┼──────────┼───────── id │ text │ │ not null │ plan │ text │ │ │ entered_on │ timestamp with time zone │ │ │ is_public │ boolean │ │ │ is_anonymized │ boolean │ │ │ title │ text │ │ │ delete_key │ text │ │ │ is_deleted │ boolean │ │ │ added_by │ text │ │ │ optimization_for │ text │ │ │ query │ text │ │ │ comments │ text │ │ │ =$ commit; COMMIT
Validation took 191ms, which is similar to how long it took to set the column NOT NULL using older approach, but the lock that was kept was only ShareUpdateExclusiveLock, which means that while some operations would still have to wait (mostly maintenance ones), normal table access, including SELECT, INSERT, UPDATE, and DELETE will work fine while the constraint is validated.
I think we should get some kind of indication that the not-null hasn't been validated (in \d output), but even without it it's great addition, as we can always check for invalid constraints “globally":
=$ select * from pg_constraint where not convalidated \gx ─[ RECORD 1 ]──┬──────────── oid │ 1689590 conname │ id_not_null connamespace │ 2200 contype │ n condeferrable │ f condeferred │ f conenforced │ t convalidated │ f conrelid │ 1134710 contypid │ 0 conindid │ 0 conparentid │ 0 confrelid │ 0 confupdtype │ confdeltype │ confmatchtype │ conislocal │ t coninhcount │ 0 connoinherit │ f conperiod │ f conkey │ {1} confkey │ [null] conpfeqop │ [null] conppeqop │ [null] conffeqop │ [null] confdelsetcols │ [null] conexclop │ [null] conbin │ [null]
Thanks to everyone that worked on it.