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

Robert,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Tue, Nov 28, 2017 at 1:59 PM, Sergei Kornilov <sk(at)zsrv(dot)org> wrote:
> > I write patch to speed up ALTER TABLE SET NOT NULL by check existed check constraints or indexes. Huge phase 3 with verify table data will be skipped if table has valid check constraint cover "alteredfield IS NOT NULL" condition or by SPI query if found index with compatible condition or regular amsearchnulls index on processed field.
>
> Doing this based on the existence of a valid constraint which implies
> that no nulls can be present seems like a good idea. Doing it based
> on an index scan doesn't necessarily seem like a good idea. We have
> no guarantee at all that the index scan will be faster than scanning
> the table would have been, and a single table scan can do multiple
> verification steps if, for example, multiple columns are set NOT NULL
> at the same time.

Isn't the first concern addressed by using SPI..?

As for the second concern, couldn't that be done with a more complicated
query through SPI, though things might have to be restructured some to
make it possible to do that.

Just, generally speaking, this is definitely something that I think we
want and neither of the above concerns seem like they're technical
reasons why we can't use something like this approach, just needs to
perhaps be reworked to handle multiple columns in a single query.

Thanks!

Stephen