On Fri, May 29, 2020 at 8:56 AM Sergei Kornilov <sk@zsrv.org> wrote:
Hello
Correct index lookup is a difficult task. I tried to implement this previously...
But the answer in SO is a bit incomplete for recent postgresql releases. Seqscan is not the only possible way to set not null in pg12+. My patch was commited ( https://commitfest.postgresql.org/22/1389/ ) and now it's possible to do this way:
alter table foos add constraint foos_not_null check (bar1 is not null) not valid; -- short-time exclusive lock
alter table foos validate constraint foos_not_null; -- still seqscan entire table but without exclusive lock
An then another short lock: alter table foos alter column bar1 set not null; alter table foos drop constraint foos_not_null;
That's really good to know, Sergei!
John, I think it's worth pointing out that Postgres most likely does a full table scan to validate a constraint by design and not in optimization oversight. Think of what's gonna happen if the index used for checking is corrupted?