On Fri, May 29, 2020 at 8:56 AM Sergei Kornilov <s...@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? Cheers, -- Alex
