Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1et7Ta-0006RC-Bp
	for pgsql-hackers@arkaria.postgresql.org; Tue, 06 Mar 2018 07:57:35 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1et7TY-0008UK-CU
	for pgsql-hackers@arkaria.postgresql.org; Tue, 06 Mar 2018 07:57:32 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <i.musin@postgrespro.ru>)
	id 1et7TY-0008UA-13
	for pgsql-hackers@lists.postgresql.org; Tue, 06 Mar 2018 07:57:32 +0000
Received: from mail.postgrespro.ru ([93.174.131.138])
	by magus.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <i.musin@postgrespro.ru>)
	id 1et7TV-0001I4-2r
	for pgsql-hackers@postgresql.org; Tue, 06 Mar 2018 07:57:31 +0000
Received: from localhost (localhost [127.0.0.1])
	by mail.postgrespro.ru (Postfix) with ESMTP id C158F21C1C96;
	Tue,  6 Mar 2018 10:57:27 +0300 (MSK)
X-Virus-Scanned: Debian amavisd-new at postgrespro.ru
X-Spam-Flag: NO
X-Spam-Score: 0
X-Spam-Level: 
X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[]
	autolearn=unavailable
Received: from [192.168.27.109] (gw.postgrespro.ru [93.174.131.141])
	(using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits))
	(Client did not present a certificate)
	by mail.postgrespro.ru (Postfix) with ESMTPSA id 7E64121C1C84;
	Tue,  6 Mar 2018 10:57:27 +0300 (MSK)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru;
	s=mail; t=1520323047;
	bh=vG/5DUlOXY9KxWMwN1yi6PAeTxNS6h8WIQH3TRcCd9A=;
	h=Subject:To:References:Cc:From:Date:In-Reply-To;
	b=D1mcz5A1ROUK1Gg+J943pr9XstrQBHs4SnImp0koklkMo/6V38VJKgPjSo8KdsFCv
	 iJ8ZVxxVGEhPgZiY4RSNA/9n2KGgeDtBFPHN1v/PskA2HpiFpMAp2mgxCiosJHE4SO
	 qmw7ebeKHDXUFRXa6uV3/eQxVIXB8txusB4GSjLs=
Subject: Re: using index or check in ALTER TABLE SET NOT NULL
To: Sergei Kornilov <sk@zsrv.org>, Stephen Frost <sfrost@snowman.net>
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>
Cc: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
From: Ildar Musin <i.musin@postgrespro.ru>
Message-ID: <8fd20248-4059-4d46-b33f-c9760cb3ddb9@postgrespro.ru>
Date: Tue, 6 Mar 2018 10:57:27 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.3.0
MIME-Version: 1.0
In-Reply-To: <354171516734367@web46g.yandex.ru>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk

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