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

From: Ildar Musin <i(dot)musin(at)postgrespro(dot)ru>
To: Sergei Kornilov <sk(at)zsrv(dot)org>, Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: using index or check in ALTER TABLE SET NOT NULL
Date: 2018-03-06 07:57:27
Message-ID: 8fd20248-4059-4d46-b33f-c9760cb3ddb9@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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(dot)musin(at)postgrespro(dot)ru

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Craig Ringer 2018-03-06 08:07:32 Re: Re: BUGFIX: standby disconnect can corrupt serialized reorder buffers
Previous Message Noah Misch 2018-03-06 07:44:51 Re: public schema default ACL