Re: feature idea: use index when checking for NULLs before SET NOT NULL

On Fri, May 29, 2020 at 8:56 AM Sergei Kornilov <sk(at)zsrv(dot)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