Re: cataloguing NOT NULL constraints

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