public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: jian he <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: L. pgsql-hackers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Subject: Re: Fix bug of CHECK constraint enforceability recursion
Date: Wed, 27 May 2026 14:20:21 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxGxY=1mxcNz8-bjp6R+ZoCRE3O7DYt2ZqObv-Le+aMOqw@mail.gmail.com>
References: <[email protected]>
<CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CACJufxGxY=1mxcNz8-bjp6R+ZoCRE3O7DYt2ZqObv-Le+aMOqw@mail.gmail.com>
> On May 26, 2026, at 16:32, jian he <[email protected]> wrote:
>
> On Tue, May 26, 2026 at 3:47 PM Chao Li <[email protected]> wrote:
>>
>>>
>>> I think this is a bug that we need to fix in 19 as well — I mean we should reject the ALTER TABLE.
>>>
>>> --
>>> Álvaro Herrera
>>
>> Thanks for your comment. Let me rework the patch.
>>
>
> Hi.
> Here are the comments placed in ATExecAlterCheckConstrEnforceability I
> came up with:
>
> + /*
> + * If the check constraint qual definitions match but their enforcement
> + * statuses conflict (parent enforced, child unenforced), it creates
> + * ambiguity around how insert operations should handle the mismatch.
> + * Therefore, we should avoid states where the parent check constraint is
> + * enforced while the child is not. We actually enforced this within
> + * MergeConstraintsIntoExisting and MergeWithExistingConstraint.
> + */
> + if (currcon->coninhcount > 0 && !recursing)
> + ereport(ERROR,
> + errcode(ERRCODE_INVALID_TABLE_DEFINITION),
> + errmsg("cannot alter inherited constraint \"%s\" of
> relation \"%s\" enforciability",
> + NameStr(currcon->conname),
> RelationGetRelationName(rel)));
>
>
>
> --
> jian
> https://www.enterprisedb.com/
> <v1-0001-disallow-alter-enforciability-of-inherited-check-constraint.patch>
Hi Jian,
Thanks for your help. Your implementation is simple and clever:
```
+ if (currcon->coninhcount > 0 && !recursing)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot alter inherited constraint \"%s\" of relation \"%s\" enforciability",
+ NameStr(currcon->conname), RelationGetRelationName(rel)));
```
Basically, it disallows all enforceability changes on inherited constraints (currcon->coninhcount > 0) at the recursion root (!recursing), or in other words, it disallows the operation on any child table.
But I see several problems with this implementation:
1. As you pointed out earlier, when a parent is ENFORCED, changing a child from ENFORCED to NOT ENFORCED should not be allowed. But when a parent is NOT ENFORCED, changing a child from NOT ENFORCED to ENFORCED should be allowed. The existing phase 3 checking also proves that.
2. Suppose a parent table is NOT ENFORCED, and a user changes a child from NOT ENFORCED to ENFORCED, which is allowed. Later, if the user wants to change the child back from ENFORCED to NOT ENFORCED, that should also be allowed. But with your v1 patch, the user would have to do the change through the parent table, which I think hurts the user experience.
3. Suppose a child table is already ENFORCED, and a user issues a command to change it to ENFORCED again, which is actually a no-op. PostgreSQL usually allows this kind of no-op, but with your v1 patch, this no-op would get an error as well, which I think also hurts the user experience.
4. It cannot handle some complicated inheritance hierarchies. For example, the following test passes with your v1:
```
evantest=# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE
evantest=# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE
evantest=#
evantest=# CREATE TABLE ch () INHERITS (p1, p2);
NOTICE: merging multiple inherited definitions of column "a"
CREATE TABLE
evantest=# ALTER TABLE p1 ALTER CONSTRAINT c NOT ENFORCED;
ALTER TABLE
```
I originally thought this should fail, but it now changes ch.c to NOT ENFORCED, so it breaks the rule because its parent p2 is still ENFORCED:
```
evantest=# SELECT conrelid::regclass, conname, conenforced, coninhcount, conislocal
evantest-# FROM pg_constraint WHERE conname = 'c';
conrelid | conname | conenforced | coninhcount | conislocal
----------+---------+-------------+-------------+------------
p1 | c | f | 0 | t
p2 | c | t | 0 | t
ch | c | f | 2 | f
(3 rows)
```
Then I realized that the initial CREATE TABLE case passes:
```
evantest=# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) NOT ENFORCED);
CREATE TABLE
evantest=# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE
evantest=# CREATE TABLE ch () INHERITS (p1, p2);
NOTICE: merging multiple inherited definitions of column "a"
CREATE TABLE
evantest=# SELECT conrelid::regclass, conname, conenforced, coninhcount, conislocal
evantest-# FROM pg_constraint WHERE conname = ‘c';
conrelid | conname | conenforced | coninhcount | conislocal
----------+---------+-------------+-------------+------------
ch | c | t | 2 | f
p1 | c | f | 0 | t
p2 | c | t | 0 | t
(3 rows)
```
When the two parents have different enforceability, the stricter one is applied to the child. So I think the test above in item 4 should also perform similar merge logic rather than fail. This seems to uncover a new issue in the original feature patch.
For the fix, my design is:
* Directly reject changing an inherited child CHECK constraint to NOT ENFORCED if an equivalent parent constraint remains ENFORCED.
* Changing a child to ENFORCED is allowed.
* During recursing, if a child also inherits an equivalent ENFORCED constraint from another parent outside the current ALTER, the child keeps the stricter ENFORCED state.
Please see my implementation in the attached v2 patch.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v2-0001-Prevent-inherited-CHECK-constraints-from-being-we.patch (22.5K, 2-v2-0001-Prevent-inherited-CHECK-constraints-from-being-we.patch)
download | inline diff:
From 3db18b528e579ef6b7bf77a5af79b33e23a15cdb Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Wed, 27 May 2026 13:49:29 +0800
Subject: [PATCH v2] Prevent inherited CHECK constraints from being weakened
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
equivalent 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:
Discussion: https://postgr.es/m/[email protected]
---
src/backend/commands/tablecmds.c | 266 ++++++++++++++++++++--
src/test/regress/expected/constraints.out | 12 +-
src/test/regress/expected/inherit.out | 54 +++++
src/test/regress/sql/constraints.sql | 5 +-
src/test/regress/sql/inherit.sql | 32 +++
5 files changed, 344 insertions(+), 25 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a1845240a98..5c3a09cc666 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,13 @@ static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cm
List **otherrelids, LOCKMODE lockmode);
static void AlterConstrUpdateConstraintEntry(ATAlterConstraint *cmdcon, Relation conrel,
HeapTuple contuple);
+static Oid ATGetEquivalentCheckConstraintOid(Relation conrel, Oid conrelid,
+ const char *conname,
+ 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 +486,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 +12494,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 +12681,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 +12707,52 @@ 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 that case, keep the child constraint ENFORCED so that
+ * its merged enforceability still reflects the remaining enforced parent.
+ */
+ if (!cmdcon->is_enforced &&
+ 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 NOT ENFORCED because "
+ "matching constraint on parent table \"%s\" is ENFORCED",
+ NameStr(currcon->conname),
+ get_rel_name(enforced_parentoid)));
+
+ 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 +12765,63 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
* try to look for it in the children.
*/
if (!recursing && !currcon->connoinherit)
+ {
children = find_all_inheritors(RelationGetRelid(rel),
lockmode, NULL);
+ 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."));
+ }
+
+ if (!cmdcon->is_enforced)
+ {
+ /*
+ * Build the set of equivalent CHECK constraints that this
+ * command will attempt to change before visiting descendants.
+ * Each descendant is compared to the original target
+ * constraint, not only by name. The root itself has already
+ * been checked above.
+ */
+ changing_conids = lappend_oid(list_copy(changing_conids),
+ currcon->oid);
+
+ foreach_oid(childoid, children)
+ {
+ if (childoid == RelationGetRelid(rel))
+ continue;
+
+ changing_conids =
+ list_append_unique_oid(changing_conids,
+ ATGetEquivalentCheckConstraintOid(conrel,
+ childoid,
+ cmdcon->conname,
+ contuple));
+ }
+ }
+ }
+
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 +12833,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 +12873,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 +12903,153 @@ 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);
}
+/*
+ * Look up a CHECK constraint by relation OID and constraint name that is
+ * equivalent to the given constraint tuple.
+ */
+static Oid
+ATGetEquivalentCheckConstraintOid(Relation conrel, Oid conrelid,
+ const char *conname, HeapTuple contuple)
+{
+ SysScanDesc scan;
+ HeapTuple tup;
+ ScanKeyData skey[3];
+ Oid conid = InvalidOid;
+
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(conrelid));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(conname));
+
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
+ NULL, 3, skey);
+
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tup);
+
+ if (con->contype == CONSTRAINT_CHECK &&
+ constraints_equivalent(tup, contuple, RelationGetDescr(conrel)))
+ {
+ conid = con->oid;
+ break;
+ }
+ }
+
+ systable_endscan(scan);
+
+ if (!OidIsValid(conid))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("equivalent CHECK constraint \"%s\" of relation \"%s\" does not exist",
+ conname, get_rel_name(conrelid)));
+
+ return conid;
+}
+
+/*
+ * 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;
+
+ 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;
+ 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 (list_member_oid(changing_conids, parentcon->oid) ||
+ parentcon->contype != CONSTRAINT_CHECK ||
+ parentcon->connoinherit ||
+ !parentcon->conenforced)
+ continue;
+
+ if (constraints_equivalent(parenttup, contuple,
+ RelationGetDescr(conrel)))
+ {
+ *enforced_parentoid = parentoid;
+ systable_endscan(pscan);
+ systable_endscan(scan);
+ table_close(inhrel, AccessShareLock);
+ return true;
+ }
+ }
+
+ 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..dada27a4cba 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -446,8 +446,12 @@ 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 because 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 because 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 because 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 +461,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..86e5b892a98 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1479,6 +1479,60 @@ 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 because 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"
+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 | p2
+(3 rows)
+
+alter table p1_c1 alter constraint p1_a_check not enforced; --error
+ERROR: cannot mark inherited constraint "p1_a_check" as NOT ENFORCED because matching constraint on parent table "p2" is ENFORCED
+drop table p1, p2 cascade;
+NOTICE: drop cascades to table p1_c1
+-- 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
--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..3803fb5c769 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -535,6 +535,38 @@ 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);
+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
+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;
+
--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)
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