From: Stephen Frost <sfrost@snowman.net>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Sergei Kornilov <sk@zsrv.org>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: using index or check in ALTER TABLE SET NOT NULL
Date: Wed, 29 Nov 2017 10:21:35 -0500
Message-ID: <20171129152135.GN4628@tamriel.snowman.net> (raw)
In-Reply-To: <CA+TgmoZKR4dTR7KyoM4jfJN2J-eNoBRG2_XHdZa1f0VUbK1xXQ@mail.gmail.com>
References: <81911511895540@web58j.yandex.ru>
<CA+TgmoZKR4dTR7KyoM4jfJN2J-eNoBRG2_XHdZa1f0VUbK1xXQ@mail.gmail.com>
Robert,
* Robert Haas (robertmhaas@gmail.com) wrote:
> On Tue, Nov 28, 2017 at 1:59 PM, Sergei Kornilov <sk@zsrv.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
Attachments:
[application/pgp-signature] signature.asc (818B, ../20171129152135.GN4628@tamriel.snowman.net/2-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: sfrost@snowman.net, robertmhaas@gmail.com, sk@zsrv.org
Subject: Re: using index or check in ALTER TABLE SET NOT NULL
In-Reply-To: <20171129152135.GN4628@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox