Re: using index or check in ALTER TABLE SET NOT NULL

On Wed, Nov 29, 2017 at 10:52 AM, Sergei Kornilov <sk(at)zsrv(dot)org> wrote:
> I agree this. Thinking a little about idea of index scan i can not give reasonable usecase which required index. My target problem of adding NOT NULL to big relation without long downtime can be done with ADD CONSTRAINT NOT VALID, VALIDATE it in second transaction, then SET NOT NULL by my patch and drop unneeded constraint.

Yes, I had the same thought.

Thinking a little bit further, maybe the idea you had in mind using
the index scan was that some indexes offer cheap ways of testing
whether ANY nulls are present in the column. For example, if we had a
non-partial btree index whose first column is the one being made NOT
NULL, we could try an index scan - via index_beginscan() /
index_getnext() / index_endscan() - trying to pull exactly one null
from the index, and judge whether or not there are nulls present based
only whether we get one. This would be a lot cheaper than scanning a
large table, but it needs some careful thought because of visibility
issues. It's not sufficient that the index contains no nulls that are
visible to our snapshot; it must contain no nulls that are visible to
any plausible current or future snapshot. I doubt that test can be
written in SQL, but it can probably be written in C. Moreover, we
need to avoid not only false negatives (thinking that there is no NULL
when there is one) but also false positives (thinking there's a NULL
in the column when there isn't, and thus failing spuriously). But it
seems like it might be useful if someone can figure out the details of
how to make it 100% correct; one index lookup is sure to be a lot
quicker than a full table scan.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company