From: Alexander Lakhin <exclusion@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Andrew Bille <andrewbille@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: cataloguing NOT NULL constraints
Date: Thu, 25 Apr 2024 08:00:00 +0300
Message-ID: <7d923a66-55f0-3395-cd40-81c142b5448b@gmail.com> (raw)
In-Reply-To: <202404241736.jf2zq2qmrgov@alvherre.pgsql>
References: <202404241736.jf2zq2qmrgov@alvherre.pgsql>
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
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: exclusion@gmail.com, alvherre@alvh.no-ip.org, dean.a.rasheed@gmail.com, andrewbille@gmail.com, peter@eisentraut.org, robertmhaas@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: cataloguing NOT NULL constraints
In-Reply-To: <7d923a66-55f0-3395-cd40-81c142b5448b@gmail.com>
* 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