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