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

Robert,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> 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.

I have to admit that the case I was thinking about was the one you
outline below..

> 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.

This was along the lines I was thinking also (though I had thought SPI
might be reasonable given our usage of it with FKs, but if it'd require
uglier warts than what SPI already has, then just finding an
appropriate index and using the internal methods would be best).

As for conflicting snapshots, isn't the lock we're taking already
AccessExclusive..?

Thanks!

Stephen