From: Ildar Musin <i.musin@postgrespro.ru>
To: Sergei Kornilov <sk@zsrv.org>
To: Stephen Frost <sfrost@snowman.net>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: using index or check in ALTER TABLE SET NOT NULL
Date: Tue, 6 Mar 2018 10:57:27 +0300
Message-ID: <8fd20248-4059-4d46-b33f-c9760cb3ddb9@postgrespro.ru> (raw)
In-Reply-To: <354171516734367@web46g.yandex.ru>
References: <81911511895540@web58j.yandex.ru>
<CA+TgmoZKR4dTR7KyoM4jfJN2J-eNoBRG2_XHdZa1f0VUbK1xXQ@mail.gmail.com>
<20171129152135.GN4628@tamriel.snowman.net>
<9878.1511970441@sss.pgh.pa.us>
<370481511970756@web3j.yandex.ru>
<491651511974381@web27j.yandex.ru>
<CA+TgmoahVfu0dUmvM1bO3csn=DtFp1fG92995rjYD9u_kO-Y1g@mail.gmail.com>
<369811512396584@web6o.yandex.ru>
<20180123153711.GI2416@tamriel.snowman.net>
<354171516734367@web46g.yandex.ru>
Hello Sergei,
I couldn't find any case when your code doesn't work properly. So it
seems ok to me.
> @@ -220,6 +220,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
> </para>
>
> <para>
> + Full table scan is performed to check that no existing row
> + in the table has null values in given column. It is possible to avoid
> + this scan by adding a valid <literal>CHECK</literal> constraint to
> + the table that would allow only NOT NULL values for given column.
> + </para>
Adding check constraint will also force the full table scan. So I think
it would be better to rephrased it as follows:
"Full table scan is performed to check that column doesn't contain NULL
values unless there are valid check constraints that prohibit NULL
values for specified column. In the latter case table scan is skipped."
A native English speaker input would be helpful here.
Regarding regression tests it may be useful to set client_min_messages
to 'debug1' before setting "NOT NULL" attribute for a column. In this
case you can tell for sure that NotNullImpliedByRelConstraints()
returned true (i.e. your code actually did the job) as the special debug
message is printed to the log.
Thanks!
--
Ildar Musin
i.musin@postgrespro.ru
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: i.musin@postgrespro.ru, sk@zsrv.org, sfrost@snowman.net, robertmhaas@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: using index or check in ALTER TABLE SET NOT NULL
In-Reply-To: <8fd20248-4059-4d46-b33f-c9760cb3ddb9@postgrespro.ru>
* 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