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

Hi

> Sergei, a few questions:
>
> - Just to be clear, your recipe does not require any indexes, right? Because the constraint check table scan is inherently concurrent?

Right. "alter table validate constraint" can not use indexes, but does not block concurrent read/write queries. Other queries in this scenario can not use indexes too, but should be fast.

> - Was this new behavior mentioned in the release nose?

Yes, small note in documentation and small note in release notes https://www.postgresql.org/docs/12/release-12.html this one:

> Allow ALTER TABLE ... SET NOT NULL to avoid unnecessary table scans (Sergei Kornilov)
> This can be optimized when the table's column constraints can be recognized as disallowing nulls.

> - Do you know if there are any blog posts etc. discussing this? (I'm definitely going to write one!!)

I do not know. Personally, I mentioned this feature in only a few Russian-speaking communities. And right now I wrote answer in dba.SO.

regards, Sergei