From d049a927b4bb1afd45642f4877b167400e683924 Mon Sep 17 00:00:00 2001 From: jian he Date: Mon, 19 Oct 2026 09:05:49 +0800 Subject: [PATCH v4 1/2] Reject enforcing a parent CHECK over a NOT VALID child ALTER CONSTRAINT ... ENFORCED on a parent marked the parent validated, but skipped a child whose constraint was ENFORCED and NOT VALID. Constraint exclusion could then drop the child's bad rows. Raise the same error that ALTER TABLE ... INHERIT raises in this case. So, we won't in a situation where the parent is validated but the child is not. Author: shihao zhong Discussion: https://postgr.es/m/CAGRkXqSGE=fQrjCoNQsyiCZRKfQ4SrB015CAR=3pXYB5tB=ieg@mail.gmail.com --- src/backend/commands/tablecmds.c | 15 +++++++++++++++ src/test/regress/expected/constraints.out | 11 +++++++++++ src/test/regress/expected/inherit.out | 22 ++++++++++++++++++++++ src/test/regress/sql/constraints.sql | 11 +++++++++++ src/test/regress/sql/inherit.sql | 14 ++++++++++++++ 5 files changed, 73 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 93ad49e59fd..20ddcad4c11 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -12727,6 +12727,21 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, target_enforced = true; } + /* + * The constraint is about to be marked validated, so a descendant that is + * enforced but not yet valid conflicts with it, as in ALTER TABLE ... + * INHERIT. + */ + if (recursing && cmdcon->is_enforced && + currcon->conenforced && !currcon->convalidated) + ereport(ERROR, + errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("constraint \"%s\" conflicts with NOT VALID constraint on child table \"%s\"", + NameStr(currcon->conname), RelationGetRelationName(rel)), + errhint("You may need to use %s on table \"%s\" first.", + "ALTER TABLE ... VALIDATE CONSTRAINT", + RelationGetRelationName(rel))); + /* * Update to the merged enforceability if needed. This may differ from the * requested enforceability when another matching parent constraint diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 83f97f684d5..dede6106a49 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -475,6 +475,17 @@ select * from check_constraint_status; drop table parted_ch; drop view check_constraint_status; +create table parted_ch( + a int, b int, + constraint cc check (a > 10) not enforced +) partition by list(a); +create table parted_ch_1(a int, b int); +alter table parted_ch_1 add constraint cc check (a > 10) not valid; +alter table parted_ch attach partition parted_ch_1 for values in (0, 1); +alter table parted_ch alter constraint cc enforced; -- error +ERROR: constraint "cc" conflicts with NOT VALID constraint on child table "parted_ch_1" +HINT: You may need to use ALTER TABLE ... VALIDATE CONSTRAINT on table "parted_ch_1" first. +drop table parted_ch; -- -- Primary keys -- diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 0136aa53c96..036fd39174f 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1479,6 +1479,28 @@ NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table p1_c1 drop cascades to table p1_c2 drop cascades to table p1_c3 +-- ALTER CONSTRAINT ... ENFORCED on a parent cannot mark the parent validated +-- while a child's constraint is enforced but NOT VALID +create table p1(f1 int constraint inh_check_constraint check (f1 > 0) not enforced); +create table p1_c1(f1 int); +alter table p1_c1 add constraint inh_check_constraint check (f1 > 0) not valid; +alter table p1_c1 inherit p1; +alter table p1 alter constraint inh_check_constraint enforced; --error +ERROR: constraint "inh_check_constraint" conflicts with NOT VALID constraint on child table "p1_c1" +HINT: You may need to use ALTER TABLE ... VALIDATE CONSTRAINT on table "p1_c1" first. +alter table p1_c1 validate constraint inh_check_constraint; +alter table p1 alter constraint inh_check_constraint enforced; --ok +select conrelid::regclass, conenforced, convalidated +from pg_constraint where conname = 'inh_check_constraint' +order by conrelid::regclass::text collate "C"; + conrelid | conenforced | convalidated +----------+-------------+-------------- + p1 | t | t + p1_c1 | t | t +(2 rows) + +drop table p1 cascade; +NOTICE: drop cascades to table p1_c1 -- an inherited CHECK constraint cannot be NOT ENFORCED under an ENFORCED parent create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); create table p1_c1() inherits(p1); diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 9705962eb9f..adb55040c71 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -319,6 +319,17 @@ select * from check_constraint_status; drop table parted_ch; drop view check_constraint_status; +create table parted_ch( + a int, b int, + constraint cc check (a > 10) not enforced +) partition by list(a); + +create table parted_ch_1(a int, b int); +alter table parted_ch_1 add constraint cc check (a > 10) not valid; +alter table parted_ch attach partition parted_ch_1 for values in (0, 1); +alter table parted_ch alter constraint cc enforced; -- error +drop table parted_ch; + -- -- Primary keys -- diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 072fca13c13..adbbbe10b9d 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -535,6 +535,20 @@ where conname = 'inh_check_constraint3' and contype = 'c' order by conrelid::regclass::text collate "C"; drop table p1 cascade; +-- ALTER CONSTRAINT ... ENFORCED on a parent cannot mark the parent validated +-- while a child's constraint is enforced but NOT VALID +create table p1(f1 int constraint inh_check_constraint check (f1 > 0) not enforced); +create table p1_c1(f1 int); +alter table p1_c1 add constraint inh_check_constraint check (f1 > 0) not valid; +alter table p1_c1 inherit p1; +alter table p1 alter constraint inh_check_constraint enforced; --error +alter table p1_c1 validate constraint inh_check_constraint; +alter table p1 alter constraint inh_check_constraint enforced; --ok +select conrelid::regclass, conenforced, convalidated +from pg_constraint where conname = 'inh_check_constraint' +order by conrelid::regclass::text collate "C"; +drop table p1 cascade; + -- an inherited CHECK constraint cannot be NOT ENFORCED under an ENFORCED parent create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); create table p1_c1() inherits(p1); -- 2.34.1