Postgresql – How to set a column to NOT NULL without locking the table during a table scan

alter-tableindexlockingnullpostgresql

(former question was: Will Postgres use a multi-column index when setting multiple colums not null?)

Typically, when I set a column not null, if it doesn't have an index then I add it first, so that postgres can (hopefully) use the index while doing the table scan while locking the table, so that the table is locked for a shorter amount of time.

I want to set several columns not null, like so:

alter table foos
  alter column bar1 set not null
  alter column bar2 set not null
  alter column bar3 set not null
  alter column bar4 set not null;

If I make a multicolumn index for these columns, will postgres use it when scanning the locked table before making this alteration?

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4);

What if I made a partial index on IS NULL (or, IS NOT NULL)

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4) where bar1 is null and bar2 is null and bar3 is null and bar4 is null;

Best Answer

Another answer from another postgresql contributor.

PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented.

Proper implementation of index scan is difficult part. We cannot just do something like this query

select exists(select from foos where bar1 is null)

from alter table command for various reasons. Such feature will require big piece of code (and, probably, fragile code under some edge cases), much work, only for limited use cases. Something what developers do not like. Actually, pgsql-hackers community does not like how NOT NULL is stored in system catalog. It would be cleaner with a redesign of this part of the catalog. After that, it would be possible to do SET NOT NULL NOT VALID with a short lock and table validation without an exclusive lock. Similar to alter table .. add constraint ... not valid + alter table .. validate constraint for check constraint or foreign key. But such redesign is much more work, and there is no one who wants to do it.

But I have good news: in PostgreSQL 12 (and above) scan the entire table is not the only option. alter table set not null can prove correctness of NOT NULL by existing check constraints. So, one can do:

-- short-time exclusive lock
alter table foos 
  add constraint foos_not_null 
  check (bar1 is not null) not valid;

-- seqscan, but without exclusive lock, concurrent sessions can read/write
alter table foos validate constraint foos_not_null;

-- exclusive lock, but foos_not_null proves: there is no NULL in this column
-- so this alter table would be fast
alter table foos alter column bar1 set not null;
-- not needed anymore
alter table foos drop constraint foos_not_null;

That was my patch. Yeah, this looks like a workaround. But, fortunately, it was merged and now it is possible to set not null without a long exclusive lock.

Related Question