Message-ID: <7d923a66-55f0-3395-cd40-81c142b5448b@gmail.com>
Date: Thu, 25 Apr 2024 08:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: cataloguing NOT NULL constraints
Content-Language: en-US
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>,
 Andrew Bille <andrewbille@gmail.com>, Peter Eisentraut
 <peter@eisentraut.org>, Robert Haas <robertmhaas@gmail.com>,
 pgsql-hackers@lists.postgresql.org
References: <202404241736.jf2zq2qmrgov@alvherre.pgsql>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <202404241736.jf2zq2qmrgov@alvherre.pgsql>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/7d923a66-55f0-3395-cd40-81c142b5448b%40gmail.com>
Precedence: bulk

24.04.2024 20:36, Alvaro Herrera wrote:
> So I added a restriction that we only accept such a change when
> recursively adding a constraint, or during binary upgrade.  This should
> limit the damage: you're no longer able to change an existing constraint
> from NO INHERIT to YES INHERIT merely by doing another ALTER TABLE ADD
> CONSTRAINT.
>
> One thing that has me a little nervous about this whole business is
> whether we're set up to error out where some child table down the
> hierarchy has nulls, and we add a not-null constraint to it but fail to
> do a verification scan.  I tried a couple of cases and AFAICS it works
> correctly, but maybe there are other cases I haven't thought about where
> it doesn't.
>

Thank you for the fix!

While studying the NO INHERIT option, I've noticed that the documentation
probably misses it's specification for NOT NULL:
https://www.postgresql.org/docs/devel/sql-createtable.html

where column_constraint is:
...
[ CONSTRAINT constraint_name ]
{ NOT NULL |
   NULL |
   CHECK ( expression ) [ NO INHERIT ] |

Also, I've found a weird behaviour with a non-inherited NOT NULL
constraint for a partitioned table:
CREATE TABLE pt(a int NOT NULL NO INHERIT) PARTITION BY LIST (a);
CREATE TABLE dp(a int NOT NULL);
ALTER TABLE pt ATTACH PARTITION dp DEFAULT;
ALTER TABLE pt DETACH PARTITION dp;
fails with:
ERROR:  relation 16389 has non-inherited constraint "dp_a_not_null"

Though with an analogous check constraint, I get:
CREATE TABLE pt(a int, CONSTRAINT nna CHECK (a IS NOT NULL) NO INHERIT) PARTITION BY LIST (a);
ERROR:  cannot add NO INHERIT constraint to partitioned table "pt"

Best regards,
Alexander