public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: Álvaro Herrera <[email protected]>
Cc: jian he <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: Fix bug of CHECK constraint enforceability recursion
Date: Tue, 9 Jun 2026 08:32:19 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
> On Jun 8, 2026, at 22:52, Álvaro Herrera <[email protected]> wrote:
>
> On 2026-Jun-08, Chao Li wrote:
>
>>> Keep errmsg() messages on a single line to improve grepability.
> G
>> I’m not sure this needs to be changed. The message is quite long, and
>> there are existing precedents for splitting long errmsg() strings
>> across multiple lines. For example:
>> ```
>> if (getExtensionOfObject(NamespaceRelationId, nspOid) == extensionOid)
>> ereport(ERROR,
>> (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
>> errmsg("cannot move extension \"%s\" into schema \"%s\" "
>> "because the extension contains the schema",
>> extensionName, newschema)));
>> ```
>>
>> So, this does not seem to be a strict rule. I also don’t think this
>> hurts grepability much in practice, since searching for the
>> distinctive part of the message still finds it.
>
> Personally I find myself upset whenever I come across messages split in
> this way. Kindly do not add more if you can avoid it. It [my dislike
> for that] is not strong enough to create a commit to stitch them back,
> but I frequently do so in my local editor and later discard the change
> when collecting changes to commit via "git add -p".
>
> Greppability is subjective -- you don't know which part is
> "distinctive". For instance, when looking for message
> refactoring/rewording I grep for things such as "cannot.*schema" or
> such, and any arbitrary splitting could potentially thwart that.
>
>
> Lastly, when the message is in a single line, grammatical mistakes are
> easier to see, such as the missing "the" in
> errmsg("cannot mark inherited constraint \"%s\" as NOT ENFORCED because [the] matching constraint on parent table \"%s\" is ENFORCED", ...)
>
Thanks for the explanation. I will treat that as a rule in future when working for PG code. So, noted.
>
> In this case I would also move the NOT ENFORCED clause out of the
> translatable message and replace it with %s; and I'm wondering whether
> the part after "because" should be errdetail or not.
In v10, I split the “because” part to a errdetail, also moved out NOT ENFORCED out of the translate message.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v10-0001-Prevent-inherited-CHECK-constraints-from-being-w.patch (25.1K, 2-v10-0001-Prevent-inherited-CHECK-constraints-from-being-w.patch)
download | inline diff:
From 9d85b9b0e646fa77be1cc5a264bf5cf384cc0d1b Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Wed, 27 May 2026 13:49:29 +0800
Subject: [PATCH v10 1/3] Prevent inherited CHECK constraints from being
weakened
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Disallow marking an inherited CHECK constraint as NOT ENFORCED when an
equivalent parent constraint remains ENFORCED. This prevents ALTER
CONSTRAINT from producing a child constraint that is weaker than one of
its inherited parent definitions.
When recursively altering a CHECK constraint to NOT ENFORCED, collect the
corresponding constraints in the affected inheritance subtree and ignore
those parent constraints while checking descendants. If a descendant also
inherits an equivalent ENFORCED constraint from a parent outside the
current ALTER, keep the descendant ENFORCED by merging to the stricter
state.
Add regression coverage for direct child ALTER, ONLY ALTER, mixed-parent
inheritance, and a common-ancestor diamond where all equivalent inherited
constraints can be changed together.
Author: Chao Li <[email protected]>
Reviewed-by: Jian He <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/commands/tablecmds.c | 246 ++++++++++++++++++++--
src/test/regress/expected/constraints.out | 15 +-
src/test/regress/expected/inherit.out | 92 ++++++++
src/test/regress/sql/constraints.sql | 5 +-
src/test/regress/sql/inherit.sql | 52 +++++
5 files changed, 385 insertions(+), 25 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a1845240a98..f22bed5f40d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -437,6 +437,7 @@ static bool ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *
static bool ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, HeapTuple contuple,
bool recurse, bool recursing,
+ List *changing_conids,
LOCKMODE lockmode);
static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
@@ -459,6 +460,7 @@ static void AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint
static void AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Oid conrelid,
bool recurse, bool recursing,
+ List *changing_conids,
LOCKMODE lockmode);
static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
@@ -466,6 +468,10 @@ static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cm
List **otherrelids, LOCKMODE lockmode);
static void AlterConstrUpdateConstraintEntry(ATAlterConstraint *cmdcon, Relation conrel,
HeapTuple contuple);
+static bool ATCheckCheckConstrHasEnforcedParent(Relation conrel, Relation rel,
+ HeapTuple contuple,
+ List *changing_conids,
+ Oid *enforced_parentoid);
static ObjectAddress ATExecValidateConstraint(List **wqueue,
Relation rel, char *constrName,
bool recurse, bool recursing, LOCKMODE lockmode);
@@ -477,6 +483,7 @@ static void QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relat
static void QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,
HeapTuple contuple, bool recurse, bool recursing,
LOCKMODE lockmode);
+static bool constraints_equivalent(HeapTuple a, HeapTuple b, TupleDesc tupleDesc);
static int transformColumnNameList(Oid relId, List *colList,
int16 *attnums, Oid *atttypids, Oid *attcollids);
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
@@ -12484,7 +12491,7 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
else if (currcon->contype == CONSTRAINT_CHECK)
changed = ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel,
contuple, recurse, false,
- lockmode);
+ NIL, lockmode);
}
else if (cmdcon->alterDeferrability &&
ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12671,12 +12678,16 @@ ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
static bool
ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, HeapTuple contuple,
- bool recurse, bool recursing, LOCKMODE lockmode)
+ bool recurse, bool recursing,
+ List *changing_conids,
+ LOCKMODE lockmode)
{
Form_pg_constraint currcon;
Relation rel;
bool changed = false;
List *children = NIL;
+ bool target_enforced = cmdcon->is_enforced;
+ Oid enforced_parentoid = InvalidOid;
/* Since this function recurses, it could be driven to stack overflow */
check_stack_depth();
@@ -12693,16 +12704,57 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
*/
rel = table_open(currcon->conrelid, NoLock);
- if (currcon->conenforced != cmdcon->is_enforced)
+ /*
+ * When setting a constraint to NOT ENFORCED, check whether any matching
+ * parent constraint remains ENFORCED and is not part of this ALTER.
+ *
+ * For a direct ALTER of an inherited constraint, reject the command,
+ * because the child cannot be weakened while its parent remains enforced.
+ *
+ * During recursion, another parent outside this ALTER may still enforce
+ * the same constraint in a regular inheritance hierarchy. In that case,
+ * keep the child constraint ENFORCED so that its merged enforceability
+ * still reflects the remaining enforced parent. Partitions do not need
+ * this recursive parent check because a partition can have only one
+ * direct parent.
+ */
+ if (!cmdcon->is_enforced &&
+ (!recursing || !rel->rd_rel->relispartition) &&
+ ATCheckCheckConstrHasEnforcedParent(conrel, rel, contuple,
+ changing_conids,
+ &enforced_parentoid))
{
- AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple);
+ if (!recursing)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot mark inherited constraint \"%s\" as %s",
+ NameStr(currcon->conname),
+ "NOT ENFORCED"),
+ errdetail("The matching constraint on parent table \"%s\" is %s.",
+ get_rel_name(enforced_parentoid), "ENFORCED"));
+
+ target_enforced = true;
+ }
+
+ /*
+ * Update to the merged enforceability if needed. This may differ from the
+ * requested enforceability when another matching parent constraint
+ * remains enforced.
+ */
+ if (currcon->conenforced != target_enforced)
+ {
+ ATAlterConstraint updatecon = *cmdcon;
+
+ updatecon.is_enforced = target_enforced;
+ AlterConstrUpdateConstraintEntry(&updatecon, conrel, contuple);
changed = true;
}
/*
* Note that we must recurse even when trying to change a check constraint
* to not enforced if it is already not enforced, in case descendant
- * constraints might be enforced and need to be changed to not enforced.
+ * constraints might be enforced and need to be changed to not enforced,
+ * unless they still inherit an enforced constraint from another parent.
* Conversely, we should do nothing if a constraint is being set to
* enforced and is already enforced, as descendant constraints cannot be
* different in that case.
@@ -12715,28 +12767,62 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
* try to look for it in the children.
*/
if (!recursing && !currcon->connoinherit)
+ {
+ Assert(changing_conids == NIL);
+
children = find_all_inheritors(RelationGetRelid(rel),
lockmode, NULL);
+ /*
+ * When setting NOT ENFORCED, build the set of equivalent CHECK
+ * constraints that this command will attempt to change before
+ * visiting descendants. The root itself has already been checked
+ * above.
+ */
+ if (!cmdcon->is_enforced)
+ changing_conids = list_make1_oid(currcon->oid);
+
+ foreach_oid(childoid, children)
+ {
+ if (childoid == RelationGetRelid(rel))
+ continue;
+
+ /*
+ * If we are told not to recurse, there had better not be any
+ * child tables, because we can't change constraint
+ * enforceability on the parent unless we have changed
+ * enforceability for all child.
+ */
+ if (!recurse)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("constraint must be altered on child tables too"),
+ errhint("Do not specify the ONLY keyword."));
+
+ /*
+ * It is sufficient to look up the constraint by name here.
+ * Supported DDL ensures that inheritable CHECK constraints
+ * with the same name have equivalent definitions when they
+ * are propagated to children or when inheritance is
+ * established.
+ */
+ if (!cmdcon->is_enforced)
+ changing_conids =
+ list_append_unique_oid(changing_conids,
+ get_relation_constraint_oid(childoid,
+ cmdcon->conname,
+ false));
+ }
+ }
+
foreach_oid(childoid, children)
{
if (childoid == RelationGetRelid(rel))
continue;
- /*
- * If we are told not to recurse, there had better not be any
- * child tables, because we can't change constraint enforceability
- * on the parent unless we have changed enforceability for all
- * child.
- */
- if (!recurse)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("constraint must be altered on child tables too"),
- errhint("Do not specify the ONLY keyword."));
-
AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel,
childoid, false, true,
+ changing_conids,
lockmode);
}
}
@@ -12748,7 +12834,7 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
*/
if (rel->rd_rel->relkind == RELKIND_RELATION &&
!currcon->conenforced &&
- cmdcon->is_enforced)
+ target_enforced)
{
AlteredTableInfo *tab;
NewConstraint *newcon;
@@ -12788,6 +12874,7 @@ static void
AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Oid conrelid,
bool recurse, bool recursing,
+ List *changing_conids,
LOCKMODE lockmode)
{
SysScanDesc pscan;
@@ -12817,11 +12904,132 @@ AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
cmdcon->conname, get_rel_name(conrelid)));
ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel, childtup,
- recurse, recursing, lockmode);
+ recurse, recursing, changing_conids,
+ lockmode);
systable_endscan(pscan);
}
+/*
+ * When setting an inherited CHECK constraint to NOT ENFORCED, look for a
+ * matching parent constraint that remains ENFORCED and is not part of the same
+ * ALTER.
+ */
+static bool
+ATCheckCheckConstrHasEnforcedParent(Relation conrel, Relation rel,
+ HeapTuple contuple,
+ List *changing_conids,
+ Oid *enforced_parentoid)
+{
+ Form_pg_constraint currcon;
+ Relation inhrel;
+ SysScanDesc scan;
+ ScanKeyData skey;
+ HeapTuple inheritsTuple;
+
+ /* Since this function recurses, it could be driven to stack overflow */
+ check_stack_depth();
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+ Assert(currcon->contype == CONSTRAINT_CHECK);
+
+ if (currcon->coninhcount <= 0)
+ return false;
+
+ inhrel = table_open(InheritsRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey,
+ Anum_pg_inherits_inhrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ scan = systable_beginscan(inhrel, InheritsRelidSeqnoIndexId,
+ true, NULL, 1, &skey);
+
+ while (HeapTupleIsValid(inheritsTuple = systable_getnext(scan)))
+ {
+ Oid parentoid;
+ Relation parentrel = NULL;
+ SysScanDesc pscan;
+ ScanKeyData pkey[3];
+ HeapTuple parenttup;
+
+ parentoid = ((Form_pg_inherits) GETSTRUCT(inheritsTuple))->inhparent;
+
+ ScanKeyInit(&pkey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(parentoid));
+ ScanKeyInit(&pkey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&pkey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ NameGetDatum(&currcon->conname));
+
+ pscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, pkey);
+
+ while (HeapTupleIsValid(parenttup = systable_getnext(pscan)))
+ {
+ Form_pg_constraint parentcon;
+
+ parentcon = (Form_pg_constraint) GETSTRUCT(parenttup);
+
+ if (parentcon->contype != CONSTRAINT_CHECK ||
+ parentcon->connoinherit ||
+ !parentcon->conenforced)
+ continue;
+
+ if (constraints_equivalent(parenttup, contuple,
+ RelationGetDescr(conrel)))
+ {
+ /*
+ * A parent listed in changing_conids is being changed by the
+ * same ALTER, but it may not have been updated yet. For
+ * regular inheritance, recurse upward to check whether an
+ * equivalent enforced parent outside the ALTER will make it
+ * remain enforced. Partitions cannot have multiple parents,
+ * so they do not need this check.
+ */
+ if (!rel->rd_rel->relispartition &&
+ list_member_oid(changing_conids, parentcon->oid))
+ {
+ Oid parent_enforced_parentoid = InvalidOid;
+
+ if (parentrel == NULL)
+ parentrel = table_open(parentoid, NoLock);
+
+ if (!ATCheckCheckConstrHasEnforcedParent(conrel,
+ parentrel,
+ parenttup,
+ changing_conids,
+ &parent_enforced_parentoid))
+ continue;
+ }
+
+ *enforced_parentoid = parentoid;
+ if (parentrel != NULL)
+ table_close(parentrel, NoLock);
+ systable_endscan(pscan);
+ systable_endscan(scan);
+ table_close(inhrel, AccessShareLock);
+ return true;
+ }
+ }
+
+ if (parentrel != NULL)
+ table_close(parentrel, NoLock);
+ systable_endscan(pscan);
+ }
+
+ systable_endscan(scan);
+ table_close(inhrel, AccessShareLock);
+
+ return false;
+}
+
/*
* Returns true if the constraint's deferrability is altered.
*
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e54fec7fb57..83f97f684d5 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -446,8 +446,15 @@ alter table parted_ch_2 alter constraint cc_2 enforced; --error
ERROR: check constraint "cc_2" of relation "parted_ch_2" is violated by some row
delete from parted_ch where a = 16;
alter table parted_ch_2 alter constraint cc_2 enforced;
-alter table parted_ch_2 alter constraint cc not enforced;
-alter table parted_ch_2 alter constraint cc_1 not enforced;
+alter table parted_ch_2 alter constraint cc not enforced; --error
+ERROR: cannot mark inherited constraint "cc" as NOT ENFORCED
+DETAIL: The matching constraint on parent table "parted_ch" is ENFORCED.
+alter table only parted_ch_2 alter constraint cc not enforced; --error
+ERROR: cannot mark inherited constraint "cc" as NOT ENFORCED
+DETAIL: The matching constraint on parent table "parted_ch" is ENFORCED.
+alter table parted_ch_2 alter constraint cc_1 not enforced; --error
+ERROR: cannot mark inherited constraint "cc_1" as NOT ENFORCED
+DETAIL: The matching constraint on parent table "parted_ch" is ENFORCED.
alter table parted_ch_2 alter constraint cc_2 not enforced;
--check these CHECK constraint status again
select * from check_constraint_status;
@@ -457,12 +464,12 @@ select * from check_constraint_status;
cc | parted_ch_1 | t | t
cc | parted_ch_11 | t | t
cc | parted_ch_12 | t | t
- cc | parted_ch_2 | f | f
+ cc | parted_ch_2 | t | t
cc_1 | parted_ch | t | t
cc_1 | parted_ch_1 | t | t
cc_1 | parted_ch_11 | t | t
cc_1 | parted_ch_12 | t | t
- cc_1 | parted_ch_2 | f | f
+ cc_1 | parted_ch_2 | t | t
cc_2 | parted_ch_2 | f | f
(11 rows)
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 3d8e8d8afd2..3c2ff55d3ba 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1479,6 +1479,98 @@ 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
+-- 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);
+alter table p1_c1 alter constraint p1_a_check not enforced; --error
+ERROR: cannot mark inherited constraint "p1_a_check" as NOT ENFORCED
+DETAIL: The matching constraint on parent table "p1" is ENFORCED.
+alter table p1 alter constraint p1_a_check not enforced; --ok
+alter table p1_c1 alter constraint p1_a_check not enforced; --ok
+drop table p1 cascade;
+NOTICE: drop cascades to table p1_c1
+-- recursive NOT ENFORCED merges with ENFORCED constraints from other parents
+create table p1(a int constraint p1_a_check check (a > 0) enforced);
+create table p2(a int constraint p1_a_check check (a > 0) enforced);
+create table p1_c1() inherits (p1, p2);
+NOTICE: merging multiple inherited definitions of column "a"
+-- make p1_c1_g2's oid smaller than p1_c1_g1's, to ensure ordering of
+-- pg_constraint rows to not impact the test results.
+create table p1_c1_g2() inherits (p1_c1);
+create table p1_c1_g1() inherits (p1_c1);
+alter table p1_c1_g2 inherit p1_c1_g1;
+create table p1_c2() inherits (p1);
+alter table p1 alter constraint p1_a_check not enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+ conname | conenforced | convalidated | conrelid
+------------+-------------+--------------+----------
+ p1_a_check | f | f | p1
+ p1_a_check | t | t | p1_c1
+ p1_a_check | t | t | p1_c1_g1
+ p1_a_check | t | t | p1_c1_g2
+ p1_a_check | f | f | p1_c2
+ p1_a_check | t | t | p2
+(6 rows)
+
+alter table p1_c1 alter constraint p1_a_check not enforced; --error
+ERROR: cannot mark inherited constraint "p1_a_check" as NOT ENFORCED
+DETAIL: The matching constraint on parent table "p2" is ENFORCED.
+alter table p2 alter constraint p1_a_check not enforced; --ok
+alter table p1_c1 alter constraint p1_a_check not enforced; --ok
+drop table p1, p2 cascade;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to table p1_c1
+drop cascades to table p1_c1_g1
+drop cascades to table p1_c1_g2
+drop cascades to table p1_c2
+-- recursive NOT ENFORCED can change all matching enforced parents together
+create table gp(a int constraint gp_a_check check (a > 0) enforced);
+create table p1() inherits (gp);
+create table p2() inherits (gp);
+create table p1_c1() inherits (p1, p2);
+NOTICE: merging multiple inherited definitions of column "a"
+alter table gp alter constraint gp_a_check not enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'gp_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+ conname | conenforced | convalidated | conrelid
+------------+-------------+--------------+----------
+ gp_a_check | f | f | gp
+ gp_a_check | f | f | p1
+ gp_a_check | f | f | p1_c1
+ gp_a_check | f | f | p2
+(4 rows)
+
+drop table gp cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table p1
+drop cascades to table p2
+drop cascades to table p1_c1
+-- recursive NOT ENFORCED can change a direct-plus-indirect diamond together
+create table gp(a int constraint gp_a_check check (a > 0) enforced);
+create table p1_c1() inherits (gp);
+create table p1() inherits (gp);
+alter table p1_c1 inherit p1;
+alter table gp alter constraint gp_a_check not enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'gp_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+ conname | conenforced | convalidated | conrelid
+------------+-------------+--------------+----------
+ gp_a_check | f | f | gp
+ gp_a_check | f | f | p1
+ gp_a_check | f | f | p1_c1
+(3 rows)
+
+drop table gp cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table p1
+drop cascades to table p1_c1
--for "no inherit" check constraint, it will not recurse to child table
create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index dc133b124bb..9705962eb9f 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -309,8 +309,9 @@ select * from check_constraint_status;
alter table parted_ch_2 alter constraint cc_2 enforced; --error
delete from parted_ch where a = 16;
alter table parted_ch_2 alter constraint cc_2 enforced;
-alter table parted_ch_2 alter constraint cc not enforced;
-alter table parted_ch_2 alter constraint cc_1 not enforced;
+alter table parted_ch_2 alter constraint cc not enforced; --error
+alter table only parted_ch_2 alter constraint cc not enforced; --error
+alter table parted_ch_2 alter constraint cc_1 not enforced; --error
alter table parted_ch_2 alter constraint cc_2 not enforced;
--check these CHECK constraint status again
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 8f986904389..072fca13c13 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -535,6 +535,58 @@ where conname = 'inh_check_constraint3' and contype = 'c'
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);
+alter table p1_c1 alter constraint p1_a_check not enforced; --error
+alter table p1 alter constraint p1_a_check not enforced; --ok
+alter table p1_c1 alter constraint p1_a_check not enforced; --ok
+drop table p1 cascade;
+
+-- recursive NOT ENFORCED merges with ENFORCED constraints from other parents
+create table p1(a int constraint p1_a_check check (a > 0) enforced);
+create table p2(a int constraint p1_a_check check (a > 0) enforced);
+create table p1_c1() inherits (p1, p2);
+-- make p1_c1_g2's oid smaller than p1_c1_g1's, to ensure ordering of
+-- pg_constraint rows to not impact the test results.
+create table p1_c1_g2() inherits (p1_c1);
+create table p1_c1_g1() inherits (p1_c1);
+alter table p1_c1_g2 inherit p1_c1_g1;
+create table p1_c2() inherits (p1);
+alter table p1 alter constraint p1_a_check not enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+alter table p1_c1 alter constraint p1_a_check not enforced; --error
+alter table p2 alter constraint p1_a_check not enforced; --ok
+alter table p1_c1 alter constraint p1_a_check not enforced; --ok
+drop table p1, p2 cascade;
+
+-- recursive NOT ENFORCED can change all matching enforced parents together
+create table gp(a int constraint gp_a_check check (a > 0) enforced);
+create table p1() inherits (gp);
+create table p2() inherits (gp);
+create table p1_c1() inherits (p1, p2);
+alter table gp alter constraint gp_a_check not enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'gp_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+drop table gp cascade;
+
+-- recursive NOT ENFORCED can change a direct-plus-indirect diamond together
+create table gp(a int constraint gp_a_check check (a > 0) enforced);
+create table p1_c1() inherits (gp);
+create table p1() inherits (gp);
+alter table p1_c1 inherit p1;
+alter table gp alter constraint gp_a_check not enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'gp_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+drop table gp cascade;
+
--for "no inherit" check constraint, it will not recurse to child table
create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
--
2.50.1 (Apple Git-155)
[application/octet-stream] v10-0002-doc-Clarify-inherited-constraint-behavior.patch (4.4K, 3-v10-0002-doc-Clarify-inherited-constraint-behavior.patch)
download | inline diff:
From bb62a3a06675ba06d10fba06ebdc63661b99c362 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Fri, 29 May 2026 06:47:07 +0800
Subject: [PATCH v10 2/3] doc: Clarify inherited constraint behavior
Update the table inheritance documentation to mention not-null constraints
alongside check constraints where inherited constraints are discussed.
Also clarify that some properties of inherited constraints can now be altered
directly on child tables, while the resulting constraint must remain compatible
with its inherited parent constraints. For multiple inheritance, say explicitly
that when a column or constraint is inherited from more than one parent, the
stricter definition applies.
Author: Chao Li <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/ddl.sgml | 33 +++++++++++++++++++--------------
1 file changed, 19 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 747f929aee3..dac31457267 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4090,7 +4090,9 @@ VALUES ('Albany', NULL, NULL, 'NY');
similar fashion. Thus, for example, a merged column will be marked
not-null if any one of the column definitions it came from is marked
not-null. Check constraints are merged if they have the same name,
- and the merge will fail if their conditions are different.
+ and the merge will fail if their conditions are different. For merged
+ check constraints, stricter enforceability is preserved: if any inherited
+ copy is enforced, the merged constraint is enforced.
</para>
<para>
@@ -4104,8 +4106,9 @@ VALUES ('Albany', NULL, NULL, 'NY');
To do this the new child table must already include columns with
the same names and types as the columns of the parent. It must also include
check constraints with the same names and check expressions as those of the
- parent. Similarly an inheritance link can be removed from a child using the
- <literal>NO INHERIT</literal> variant of <command>ALTER TABLE</command>.
+ parent, as well as matching not-null constraints. Similarly an inheritance
+ link can be removed from a child using the <literal>NO INHERIT</literal>
+ variant of <command>ALTER TABLE</command>.
Dynamically adding and removing inheritance links like this can be useful
when the inheritance relationship is being used for table
partitioning (see <xref linkend="ddl-partitioning"/>).
@@ -4124,21 +4127,23 @@ VALUES ('Albany', NULL, NULL, 'NY');
<para>
A parent table cannot be dropped while any of its children remain. Neither
- can columns or check constraints of child tables be dropped or altered
- if they are inherited
- from any parent tables. If you wish to remove a table and all of its
- descendants, one easy way is to drop the parent table with the
- <literal>CASCADE</literal> option (see <xref linkend="ddl-depend"/>).
+ can inherited columns or inherited check and not-null constraints of child
+ tables be dropped directly. Some properties of inherited constraints can
+ be altered, but each resulting constraint must remain compatible with all
+ parent constraints from which it is inherited. If you wish to remove a
+ table and all of its descendants, one easy way is to drop the parent table
+ with the <literal>CASCADE</literal> option (see <xref linkend="ddl-depend"/>).
</para>
<para>
<command>ALTER TABLE</command> will
- propagate any changes in column data definitions and check
- constraints down the inheritance hierarchy. Again, dropping
- columns that are depended on by other tables is only possible when using
- the <literal>CASCADE</literal> option. <command>ALTER
- TABLE</command> follows the same rules for duplicate column merging
- and rejection that apply during <command>CREATE TABLE</command>.
+ propagate changes in column definitions and in inheritable constraints
+ (check and not-null constraints) down the inheritance hierarchy. Again,
+ dropping columns that are depended on by other tables is only possible
+ when using the <literal>CASCADE</literal> option. <command>ALTER
+ TABLE</command> follows the same rules for merging or rejecting duplicate
+ inherited column and constraint definitions that apply during
+ <command>CREATE TABLE</command>.
</para>
<para>
--
2.50.1 (Apple Git-155)
[application/octet-stream] v10-0003-doc-Clarify-ALTER-CONSTRAINT-enforceability-beha.patch (2.3K, 4-v10-0003-doc-Clarify-ALTER-CONSTRAINT-enforceability-beha.patch)
download | inline diff:
From b644a1d1c40d731a8c46d4400e4137bed320444b Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Mon, 25 May 2026 16:59:21 +0800
Subject: [PATCH v10 3/3] doc: Clarify ALTER CONSTRAINT enforceability behavior
The ALTER TABLE documentation said that FOREIGN KEY and CHECK
constraints may be altered, but did not distinguish between
deferrability and enforceability attributes.
Clarify that deferrability attributes can currently be altered only for
FOREIGN KEY constraints, while enforceability can be altered for both
FOREIGN KEY and CHECK constraints. Also document that setting a
constraint to ENFORCED verifies existing rows and resumes checking new
or updated rows.
Author: Chao Li <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/ref/alter_table.sgml | 14 ++++++++++++--
1 file changed, 12 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index dec34337d1a..af247d82902 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -586,8 +586,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently <literal>FOREIGN KEY</literal> and <literal>CHECK</literal>
- constraints may be altered in this fashion, but see below.
+ created. Currently, the deferrability attributes can be altered only
+ for <literal>FOREIGN KEY</literal> constraints. The enforceability
+ attribute can be altered for <literal>FOREIGN KEY</literal> and
+ <literal>CHECK</literal> constraints.
+ </para>
+
+ <para>
+ Setting a constraint to <literal>NOT ENFORCED</literal> causes the
+ database system to stop checking it for new or updated rows. Setting
+ a constraint to <literal>ENFORCED</literal> causes the database system
+ to verify that existing rows satisfy the constraint and to check it
+ for new or updated rows.
</para>
</listitem>
</varlistentry>
--
2.50.1 (Apple Git-155)
view thread (32+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Fix bug of CHECK constraint enforceability recursion
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox