On Mon, Dec 8, 2025 at 5:58 PM Amul Sul <[email protected]> wrote:
>
>
> The v4 patch is quite good. Here are a few comments/suggestions for
> the cosmetic fixes:
>
> + created. Currently <literal>FOREIGN KEY</literal> and
> + <literal>CHECK</literal> constraints may be altered in this
> fashion, but see below.
>
> Although documents may not strictly follow an 80-column length
> restriction all the places, it is better to adhere to it as much as possible.
> --
>
> + errhint("Only foreign key and check constraints can
> change enforceability"));
>
> Missing a full stop (.) at the end.
> --
>
> + /*
> + * parent relation already locked by called, children will be locked by
> + * find_all_inheritors. So NoLock is fine here.
> + */
> + rel = table_open(currcon->conrelid, NoLock);
> + if (currcon->conenforced != cmdcon->is_enforced)
> + {
>
> Add a newline between these. Also, start comment with capital letter:
> s/parent/Parent
> --
>
> -static bool ATExecAlterConstrEnforceability(List **wqueue,
> ...
> +static bool ATExecAlterFKConstrEnforceability(List **wqueue,
>
> I suggest the renaming patch be separated.
> --
>
> - * Currently only works for Foreign Key and not null constraints.
> + * Currently works for Foreign Key, CHECK, and not null constraints.
>
> Not consistent naming format, should be: s/CHECK/Check.
> --
>
> + if (cmdcon->alterEnforceability)
> + {
> + if (currcon->contype == CONSTRAINT_FOREIGN)
> + {
> + ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
> + currcon->conrelid,
> currcon->confrelid,
> + contuple, lockmode, InvalidOid,
> + InvalidOid, InvalidOid,
> InvalidOid);
> + changed = true;
> + }
> + else if (currcon->contype == CONSTRAINT_CHECK)
> + {
> + ATExecAlterCheckConstrEnforceability(wqueue, cmdcon,
> conrel, contuple,
> + recurse, false, lockmode);
> + changed = true;
> + }
> + }
>
> Don't need inner curly braces; set changed = true; once for both.
> --
>
> + * conrel is the pg_constraint catalog relation.
>
> Not sure why we need to mention conrel here only?
> --
>
hi.
I have addressed all your points mentioned above.
> + if (!cmdcon->is_enforced || changed)
> + {
>
> The reason for recursing for the non-enforced constraint (like the FK
> constraint) is mentioned in the function prolog. However, since two
> conditions are involved here, I was initially confused about the
> change. Could you please add a short comment explaining why we enter
> for the not-enforced constraint irrespective of whether it was changed
> or not, or simply move the relevant note from the prolog here?
> --
moving the prolog to the IF check seems easier.
>
> +static void
> +AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint
> *cmdcon,
> + Relation conrel, Oid conrelid,
> + bool recurse, bool recursing,
> + LOCKMODE lockmode)
> +{
>
> Kindly add a prolog comment.
>
/*
* Invokes ATExecAlterCheckConstrEnforceability for each CHECK constraint that
* is a child of the specified constraint.
*
* We rely on the parent and child tables having identical CHECK constraint
* names to retrieve the child's pg_constraint tuple.
*
* The arguments to this function have the same meaning as the arguments to
* ATExecAlterCheckConstrEnforceability.
*/
The above comments are what I came up with.
v5-0001:
AlterConstrEnforceabilityRecurse renamed to AlterFKConstrEnforceabilityRecurse
ATExecAlterConstrEnforceability renamed to ATExecAlterFKConstrEnforceability.
comments slightly adjusted, no other changes.
v5-0002: alter check constraint enforceability
--
jian
https://www.enterprisedb.com/
From dd3ef63485d6a438ca3af1664696b9d265de91b3 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 11 Dec 2025 13:10:51 +0800
Subject: [PATCH v5 1/2] rename "AlterConstrEnforceability" to
"AlterFKConstrEnforceability"
AlterConstrEnforceabilityRecurse renamed to AlterFKConstrEnforceabilityRecurse
ATExecAlterConstrEnforceability renamed to ATExecAlterFKConstrEnforceability.
commitfest: https://commitfest.postgresql.org/patch/5796
context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
discussion: https://postgr.es/m/cacjufxhch_fu-fsewscvg9mn6-5tzr6h9ntn+0kugtcaerd...@mail.gmail.com
---
src/backend/commands/tablecmds.c | 117 ++++++++++++++++---------------
1 file changed, 59 insertions(+), 58 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1c9ef53be20..752634018c2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -396,14 +396,14 @@ static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel,
static bool ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel,
Relation tgrel, Relation rel, HeapTuple contuple,
bool recurse, LOCKMODE lockmode);
-static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
- Relation conrel, Relation tgrel,
- Oid fkrelid, Oid pkrelid,
- HeapTuple contuple, LOCKMODE lockmode,
- Oid ReferencedParentDelTrigger,
- Oid ReferencedParentUpdTrigger,
- Oid ReferencingParentInsTrigger,
- Oid ReferencingParentUpdTrigger);
+static bool ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ Oid fkrelid, Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger);
static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
HeapTuple contuple, bool recurse,
@@ -414,14 +414,14 @@ static bool ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cm
static void AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
bool deferrable, bool initdeferred,
List **otherrelids);
-static void AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
- Relation conrel, Relation tgrel,
- Oid fkrelid, Oid pkrelid,
- HeapTuple contuple, LOCKMODE lockmode,
- Oid ReferencedParentDelTrigger,
- Oid ReferencedParentUpdTrigger,
- Oid ReferencingParentInsTrigger,
- Oid ReferencingParentUpdTrigger);
+static void AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ Oid fkrelid, Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger);
static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
HeapTuple contuple, bool recurse,
@@ -12349,15 +12349,15 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
* enforceability, we don't need to explicitly update multiple entries in
* pg_trigger related to deferrability.
*
- * Modifying enforceability involves either creating or dropping the
- * trigger, during which the deferrability setting will be adjusted
+ * Modifying foreign key enforceability involves either creating or dropping
+ * the trigger, during which the deferrability setting will be adjusted
* automatically.
*/
if (cmdcon->alterEnforceability &&
- ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
- currcon->conrelid, currcon->confrelid,
- contuple, lockmode, InvalidOid,
- InvalidOid, InvalidOid, InvalidOid))
+ ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
+ currcon->conrelid, currcon->confrelid,
+ contuple, lockmode, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid))
changed = true;
else if (cmdcon->alterDeferrability &&
@@ -12389,7 +12389,7 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
}
/*
- * Returns true if the constraint's enforceability is altered.
+ * Returns true if the foreign key constraint's enforceability is altered.
*
* Depending on whether the constraint is being set to ENFORCED or NOT
* ENFORCED, it creates or drops the trigger accordingly.
@@ -12401,14 +12401,14 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
* enforced, as descendant constraints cannot be different in that case.
*/
static bool
-ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
- Relation conrel, Relation tgrel,
- Oid fkrelid, Oid pkrelid,
- HeapTuple contuple, LOCKMODE lockmode,
- Oid ReferencedParentDelTrigger,
- Oid ReferencedParentUpdTrigger,
- Oid ReferencingParentInsTrigger,
- Oid ReferencingParentUpdTrigger)
+ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ Oid fkrelid, Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger)
{
Form_pg_constraint currcon;
Oid conoid;
@@ -12444,10 +12444,10 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
*/
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
- AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
- fkrelid, pkrelid, contuple,
- lockmode, InvalidOid, InvalidOid,
- InvalidOid, InvalidOid);
+ AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
+ fkrelid, pkrelid, contuple,
+ lockmode, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid);
/* Drop all the triggers */
DropForeignKeyConstraintTriggers(tgrel, conoid, InvalidOid, InvalidOid);
@@ -12523,12 +12523,13 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
*/
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
- AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
- fkrelid, pkrelid, contuple,
- lockmode, ReferencedDelTriggerOid,
- ReferencedUpdTriggerOid,
- ReferencingInsTriggerOid,
- ReferencingUpdTriggerOid);
+ AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
+ fkrelid, pkrelid, contuple,
+ lockmode,
+ ReferencedDelTriggerOid,
+ ReferencedUpdTriggerOid,
+ ReferencingInsTriggerOid,
+ ReferencingUpdTriggerOid);
}
table_close(rel, NoLock);
@@ -12741,25 +12742,25 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
}
/*
- * Invokes ATExecAlterConstrEnforceability for each constraint that is a child of
- * the specified constraint.
+ * Invokes ATExecAlterFKConstrEnforceability for each foreign key constraint
+ * that is a child of the specified constraint.
*
* Note that this doesn't handle recursion the normal way, viz. by scanning the
* list of child relations and recursing; instead it uses the conparentid
* relationships. This may need to be reconsidered.
*
* The arguments to this function have the same meaning as the arguments to
- * ATExecAlterConstrEnforceability.
+ * ATExecAlterFKConstrEnforceability.
*/
static void
-AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
- Relation conrel, Relation tgrel,
- Oid fkrelid, Oid pkrelid,
- HeapTuple contuple, LOCKMODE lockmode,
- Oid ReferencedParentDelTrigger,
- Oid ReferencedParentUpdTrigger,
- Oid ReferencingParentInsTrigger,
- Oid ReferencingParentUpdTrigger)
+AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ Oid fkrelid, Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger)
{
Form_pg_constraint currcon;
Oid conoid;
@@ -12779,12 +12780,12 @@ AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
true, NULL, 1, &pkey);
while (HeapTupleIsValid(childtup = systable_getnext(pscan)))
- ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
- pkrelid, childtup, lockmode,
- ReferencedParentDelTrigger,
- ReferencedParentUpdTrigger,
- ReferencingParentInsTrigger,
- ReferencingParentUpdTrigger);
+ ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
+ pkrelid, childtup, lockmode,
+ ReferencedParentDelTrigger,
+ ReferencedParentUpdTrigger,
+ ReferencingParentInsTrigger,
+ ReferencingParentUpdTrigger);
systable_endscan(pscan);
}
--
2.34.1
From 8c760261cc71bbd584dc2b6e9ca418e02f5cd6c8 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 11 Dec 2025 14:44:23 +0800
Subject: [PATCH v5 2/2] alter check constraint enforceability
syntax: ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
commitfest: https://commitfest.postgresql.org/patch/5796
context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
discussion: https://postgr.es/m/cacjufxhch_fu-fsewscvg9mn6-5tzr6h9ntn+0kugtcaerd...@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 4 +-
src/backend/commands/tablecmds.c | 195 ++++++++++++++++++++--
src/test/regress/expected/constraints.out | 79 +++++++++
src/test/regress/expected/inherit.out | 62 +++++++
src/test/regress/sql/constraints.sql | 51 ++++++
src/test/regress/sql/inherit.sql | 43 +++++
6 files changed, 422 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 9d23ad5a0fb..b0cceba41c4 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -569,8 +569,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered in
- this fashion, but see below.
+ created. Currently <literal>FOREIGN KEY</literal> and <literal>CHECK</literal>
+ constraints may be altered in this fashion, but see below.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 752634018c2..7b207f12f0c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -404,6 +404,10 @@ static bool ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *
Oid ReferencedParentUpdTrigger,
Oid ReferencingParentInsTrigger,
Oid ReferencingParentUpdTrigger);
+static bool ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, HeapTuple contuple,
+ bool recurse, bool recursing,
+ LOCKMODE lockmode);
static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
HeapTuple contuple, bool recurse,
@@ -422,6 +426,10 @@ static void AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint
Oid ReferencedParentUpdTrigger,
Oid ReferencingParentInsTrigger,
Oid ReferencingParentUpdTrigger);
+static void AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Oid conrelid,
+ bool recurse, bool recursing,
+ LOCKMODE lockmode);
static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
HeapTuple contuple, bool recurse,
@@ -12181,7 +12189,7 @@ GetForeignKeyCheckTriggers(Relation trigrel,
*
* Update the attributes of a constraint.
*
- * Currently only works for Foreign Key and not null constraints.
+ * Currently works for Foreign Key, Check, and not null constraints.
*
* If the constraint is modified, returns its address; otherwise, return
* InvalidObjectAddress.
@@ -12243,11 +12251,13 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
- if (cmdcon->alterEnforceability && currcon->contype != CONSTRAINT_FOREIGN)
+ if (cmdcon->alterEnforceability &&
+ (currcon->contype != CONSTRAINT_FOREIGN && currcon->contype != CONSTRAINT_CHECK))
ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"",
- cmdcon->conname, RelationGetRelationName(rel))));
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"",
+ cmdcon->conname, RelationGetRelationName(rel)),
+ errhint("Only foreign key and check constraints can change enforceability."));
if (cmdcon->alterInheritability &&
currcon->contype != CONSTRAINT_NOTNULL)
ereport(ERROR,
@@ -12353,12 +12363,21 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
* the trigger, during which the deferrability setting will be adjusted
* automatically.
*/
- if (cmdcon->alterEnforceability &&
- ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
- currcon->conrelid, currcon->confrelid,
- contuple, lockmode, InvalidOid,
- InvalidOid, InvalidOid, InvalidOid))
+ if (cmdcon->alterEnforceability)
+ {
+ if (currcon->contype == CONSTRAINT_FOREIGN)
+ ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
+ currcon->conrelid,
+ currcon->confrelid,
+ contuple, lockmode,
+ InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid);
+ else if (currcon->contype == CONSTRAINT_CHECK)
+ ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel,
+ contuple, recurse, false,
+ lockmode);
changed = true;
+ }
else if (cmdcon->alterDeferrability &&
ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12537,6 +12556,162 @@ ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
return changed;
}
+ /*
+ * Returns true if the CHECK constraint's enforceability is altered.
+ */
+static bool
+ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, HeapTuple contuple,
+ bool recurse, bool recursing, LOCKMODE lockmode)
+{
+ Form_pg_constraint currcon;
+ Relation rel;
+ bool changed = false;
+ List *children = NIL;
+
+ /* Since this function recurses, it could be driven to stack overflow */
+ check_stack_depth();
+
+ Assert(cmdcon->alterEnforceability);
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+ Assert(currcon->contype == CONSTRAINT_CHECK);
+
+ /*
+ * Parent relation already locked by called, children will be locked by
+ * find_all_inheritors. So NoLock is fine here.
+ */
+ rel = table_open(currcon->conrelid, NoLock);
+
+ if (currcon->conenforced != cmdcon->is_enforced)
+ {
+ AlterConstrUpdateConstraintEntry(cmdcon, 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.
+ * 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.
+ */
+ if (!cmdcon->is_enforced || changed)
+ {
+ /*
+ * If we're recursing, the parent has already done this, so skip it.
+ * Also, if the constraint is a NO INHERIT constraint, we shouldn't 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."));
+
+ AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel,
+ childoid, false, true,
+ lockmode);
+ }
+ }
+
+ /*
+ * Tell Phase 3 to check that the constraint is satisfied by existing rows.
+ * This is needed only when altering the constraint from NOT ENFORCED to
+ * ENFORCED.
+ */
+ if (rel->rd_rel->relkind == RELKIND_RELATION &&
+ !currcon->conenforced &&
+ cmdcon->is_enforced)
+ {
+ AlteredTableInfo *tab;
+ NewConstraint *newcon;
+ Datum val;
+ char *conbin;
+
+ newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+ newcon->name = pstrdup(NameStr(currcon->conname));
+ newcon->contype = CONSTR_CHECK;
+ val = SysCacheGetAttrNotNull(CONSTROID, contuple,
+ Anum_pg_constraint_conbin);
+ conbin = TextDatumGetCString(val);
+ newcon->qual = expand_generated_columns_in_expr(stringToNode(conbin), rel, 1);
+
+ /* Find or create work queue entry for this table */
+ tab = ATGetQueueEntry(wqueue, rel);
+ tab->constraints = lappend(tab->constraints, newcon);
+ }
+
+ table_close(rel, NoLock);
+
+ return changed;
+}
+
+/*
+ * Invokes ATExecAlterCheckConstrEnforceability for each CHECK constraint that
+ * is a child of the specified constraint.
+ *
+ * We rely on the parent and child tables having identical CHECK constraint
+ * names to retrieve the child's pg_constraint tuple.
+ *
+ * The arguments to this function have the same meaning as the arguments to
+ * ATExecAlterCheckConstrEnforceability.
+ */
+static void
+AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Oid conrelid,
+ bool recurse, bool recursing,
+ LOCKMODE lockmode)
+{
+ SysScanDesc pscan;
+ HeapTuple childtup;
+ ScanKeyData skey[3];
+
+ 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(cmdcon->conname));
+
+ pscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
+ NULL, 3, skey);
+
+ if (!HeapTupleIsValid(childtup = systable_getnext(pscan)))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ cmdcon->conname, get_rel_name(conrelid)));
+
+ ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel, childtup,
+ recurse, recursing, lockmode);
+
+ systable_endscan(pscan);
+}
+
+
/*
* 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 1bbf59cca02..c0cf19d0eb0 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -390,6 +390,83 @@ SELECT * FROM COPY_TBL;
6 | OK | 4
(2 rows)
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+create table parted_ch(
+ a int, b int,
+ constraint cc check (a > 10) not enforced,
+ constraint cc_1 check (b < 17) not enforced
+) partition by range(a);
+create table parted_ch_1 partition of parted_ch for values from (0) to (10) partition by list(b);
+create table parted_ch_11 partition of parted_ch_1 for values in (0, 1, 22,4);
+create table parted_ch_12 partition of parted_ch_1 for values in (2);
+create table parted_ch_2(b int, a int,
+ constraint cc check (a > 10) not enforced,
+ constraint cc_1 check (b < 17) enforced,
+ constraint cc_2 check( a < 15) not enforced
+);
+alter table parted_ch attach partition parted_ch_2 for values from (10) to (20);
+insert into parted_ch values (1, 22), (9, 1), (16, 16);
+alter table parted_ch alter constraint cc_1 enforced; --error
+ERROR: check constraint "cc_1" of relation "parted_ch_11" is violated by some row
+update parted_ch set b = 4 where b = 22;
+alter table parted_ch alter constraint cc_1 enforced; --ok
+create or replace view check_constraint_status as
+select conname, conrelid::regclass, conenforced, convalidated
+from pg_constraint
+where conrelid::regclass::text ~* '^parted_ch' and contype = 'c'
+order by conname, conrelid::regclass::text collate "C";
+alter table parted_ch alter constraint cc not enforced; --no-op
+alter table parted_ch alter constraint cc enforced; --error
+ERROR: check constraint "cc" of relation "parted_ch_11" is violated by some row
+delete from parted_ch where a = 1;
+alter table parted_ch alter constraint cc enforced; --error
+ERROR: check constraint "cc" of relation "parted_ch_11" is violated by some row
+delete from parted_ch where a = 9;
+alter table parted_ch alter constraint cc enforced;
+--check these CHECK constraint status
+select * from check_constraint_status;
+ conname | conrelid | conenforced | convalidated
+---------+--------------+-------------+--------------
+ cc | parted_ch | t | t
+ cc | parted_ch_1 | t | t
+ cc | parted_ch_11 | t | t
+ cc | parted_ch_12 | t | t
+ 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 | t | t
+ cc_2 | parted_ch_2 | f | f
+(11 rows)
+
+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_2 not enforced;
+--check these CHECK constraint status again
+select * from check_constraint_status;
+ conname | conrelid | conenforced | convalidated
+---------+--------------+-------------+--------------
+ cc | parted_ch | t | t
+ 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_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_2 | parted_ch_2 | f | f
+(11 rows)
+
+drop table parted_ch;
--
-- Primary keys
--
@@ -746,8 +823,10 @@ LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
^
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
+HINT: Only foreign key and check constraints can change enforceability.
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED;
ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
+HINT: Only foreign key and check constraints can change enforceability.
-- can't make an existing constraint NOT VALID
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT VALID;
ERROR: constraints cannot be altered to be NOT VALID
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 0490a746555..36f81f39265 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1421,11 +1421,54 @@ order by 1, 2;
p1_c3 | inh_check_constraint9 | f | 2 | t | t
(38 rows)
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+ERROR: constraint must be altered on child tables too
+HINT: Do not specify the ONLY keyword.
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+ERROR: constraint must be altered on child tables too
+HINT: Do not specify the ONLY keyword.
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+alter table p1 alter constraint inh_check_constraint3 enforced; --error
+ERROR: check constraint "inh_check_constraint3" of relation "p1_c1" is violated by some row
+delete from only p1_c1 where f1 = -2;
+alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error
+ERROR: check constraint "inh_check_constraint3" of relation "p1_c3" is violated by some row
+delete from only p1_c3 where f1 = -3;
+alter table p1 alter constraint inh_check_constraint3 enforced; --ok
+alter table p1 alter constraint inh_check_constraint3 not enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'inh_check_constraint3' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+ conname | conenforced | convalidated | conrelid
+-----------------------+-------------+--------------+----------
+ inh_check_constraint3 | f | f | p1
+ inh_check_constraint3 | f | f | p1_c1
+ inh_check_constraint3 | f | f | p1_c2
+ inh_check_constraint3 | f | f | p1_c3
+(4 rows)
+
drop table p1 cascade;
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
+--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);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
+ERROR: check constraint "p1_a_check" of relation "p1_c1" is violated by some row
+drop table p1 cascade;
+NOTICE: drop cascades to table p1_c1
--
-- Similarly, check the merging of existing constraints; a parent constraint
-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
@@ -1434,6 +1477,25 @@ drop cascades to table p1_c3
create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+ERROR: new row for relation "p1_c1" violates check constraint "p1_a_check"
+DETAIL: Failing row contains (-1).
+alter table p1 alter constraint p1_a_check enforced; --error
+ERROR: check constraint "p1_a_check" of relation "p1" is violated by some row
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+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 | f | f | p1_c1
+(2 rows)
+
drop table p1 cascade;
NOTICE: drop cascades to table p1_c1
create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 733a1dbccfe..514ef4b2a50 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -266,6 +266,57 @@ COPY COPY_TBL FROM :'filename';
SELECT * FROM COPY_TBL;
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+create table parted_ch(
+ a int, b int,
+ constraint cc check (a > 10) not enforced,
+ constraint cc_1 check (b < 17) not enforced
+) partition by range(a);
+create table parted_ch_1 partition of parted_ch for values from (0) to (10) partition by list(b);
+create table parted_ch_11 partition of parted_ch_1 for values in (0, 1, 22,4);
+create table parted_ch_12 partition of parted_ch_1 for values in (2);
+create table parted_ch_2(b int, a int,
+ constraint cc check (a > 10) not enforced,
+ constraint cc_1 check (b < 17) enforced,
+ constraint cc_2 check( a < 15) not enforced
+);
+alter table parted_ch attach partition parted_ch_2 for values from (10) to (20);
+
+insert into parted_ch values (1, 22), (9, 1), (16, 16);
+
+alter table parted_ch alter constraint cc_1 enforced; --error
+update parted_ch set b = 4 where b = 22;
+alter table parted_ch alter constraint cc_1 enforced; --ok
+
+create or replace view check_constraint_status as
+select conname, conrelid::regclass, conenforced, convalidated
+from pg_constraint
+where conrelid::regclass::text ~* '^parted_ch' and contype = 'c'
+order by conname, conrelid::regclass::text collate "C";
+
+alter table parted_ch alter constraint cc not enforced; --no-op
+alter table parted_ch alter constraint cc enforced; --error
+delete from parted_ch where a = 1;
+alter table parted_ch alter constraint cc enforced; --error
+delete from parted_ch where a = 9;
+alter table parted_ch alter constraint cc enforced;
+
+--check these CHECK constraint status
+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_2 not enforced;
+
+--check these CHECK constraint status again
+select * from check_constraint_status;
+drop table parted_ch;
+
--
-- Primary keys
--
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..8f986904389 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -510,6 +510,38 @@ select conrelid::regclass::text as relname, conname, conislocal, coninhcount, co
from pg_constraint where conname like 'inh\_check\_constraint%'
order by 1, 2;
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+
+alter table p1 alter constraint inh_check_constraint3 enforced; --error
+delete from only p1_c1 where f1 = -2;
+alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error
+
+delete from only p1_c3 where f1 = -3;
+alter table p1 alter constraint inh_check_constraint3 enforced; --ok
+alter table p1 alter constraint inh_check_constraint3 not enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'inh_check_constraint3' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+drop table p1 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);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
drop table p1 cascade;
--
@@ -520,6 +552,17 @@ drop table p1 cascade;
create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+alter table p1 alter constraint p1_a_check enforced; --error
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+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";
drop table p1 cascade;
create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
--
2.34.1
