On Wed, Feb 11, 2026 at 3:37 AM Zsolt Parragi <[email protected]> wrote:
>
> Hello
>
> - 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;
>
> Isn't this a behavior change?
>
Hi.
You are right, I missed this minor detail.
Now, I changed it to
+ if (cmdcon->alterEnforceability)
+ {
+ if (currcon->contype == CONSTRAINT_FOREIGN)
+ changed = ATExecAlterFKConstrEnforceability(wqueue,
cmdcon, conrel, tgrel,
+ currcon->conrelid,
+ currcon->confrelid,
+ contuple, lockmode,
+ InvalidOid, InvalidOid,
+ InvalidOid,
InvalidOid);
+ else if (currcon->contype == CONSTRAINT_CHECK)
+ changed = ATExecAlterCheckConstrEnforceability(wqueue,
cmdcon, conrel,
+ contuple,
recurse, false,
+ lockmode);
+ }
--
jian
https://www.enterprisedb.com/
From 68a04f562ca136ecb7c7f89dd4662d32557bbdbd Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 16 Dec 2025 12:28:46 +0800
Subject: [PATCH v7 1/2] rename alter constraint enforceability related
functions
The functions AlterConstrEnforceabilityRecurse and
ATExecAlterConstrEnforceability are being renamed to
AlterFKConstrEnforceabilityRecurse and ATExecAlterFKConstrEnforceability,
respectively.
The current alter constraint functions only handle Foreign Key constraints.
Renaming them to be more explicit about the constraint type is necessary;
otherwise, it will cause confusion when we later introduce the ability to alter
the enforceability of other constraints.
Author: jian he <[email protected]>
Reviewed-by: Amul Sul <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>,
Reviewed-by: Robert Treat <[email protected]>
(XXX delete this line later) commitfest: https://commitfest.postgresql.org/patch/5796
discussion: https://postgr.es/m/cacjufxhch_fu-fsewscvg9mn6-5tzr6h9ntn+0kugtcaerd...@mail.gmail.com
---
src/backend/commands/tablecmds.c | 119 ++++++++++++++++---------------
1 file changed, 60 insertions(+), 59 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index df1ba112b35..dcc4ba7f30a 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,
@@ -12397,15 +12397,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
- * automatically.
+ * 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 &&
@@ -12437,7 +12437,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.
@@ -12449,14 +12449,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;
@@ -12492,10 +12492,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);
@@ -12571,12 +12571,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);
@@ -12789,25 +12790,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;
@@ -12827,12 +12828,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 3e315a50be90bfb6a968837ea96c0da5354bfd55 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sat, 21 Feb 2026 22:07:47 +0800
Subject: [PATCH v7 2/2] Add support for altering CHECK constraint
enforceability
This expands the capability of ALTER TABLE ALTER CONSTRAINT to include CHECK
constraints; previously, altering constraint enforceability was only supported
for foreign key constraints (as of commit eec0040).
Altering a CHECK constraint from NOT ENFORCED to ENFORCED not only update
catalog information, also perform a full table scan to validate existing data.
Conversely, changing an ENFORCED CHECK constraint to NOT ENFORCED does not
require a table scan, as it will only update catalog information.
Author: jian he <[email protected]>
Reviewed-by: Amul Sul <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>,
Reviewed-by: Robert Treat <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
(XXX delete this line later) commitfest: https://commitfest.postgresql.org/patch/5796
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 | 193 +++++++++++++++++++++-
src/test/regress/expected/constraints.out | 80 +++++++++
src/test/regress/expected/inherit.out | 62 +++++++
src/test/regress/sql/constraints.sql | 52 ++++++
src/test/regress/sql/inherit.sql | 43 +++++
6 files changed, 423 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..fd3cf97a615 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -575,8 +575,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 dcc4ba7f30a..b30cfbde6db 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,
@@ -12229,7 +12237,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.
@@ -12291,11 +12299,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))));
+ cmdcon->conname, RelationGetRelationName(rel)),
+ errhint("Only foreign key and check constraints can change enforceability.")));
if (cmdcon->alterInheritability &&
currcon->contype != CONSTRAINT_NOTNULL)
ereport(ERROR,
@@ -12401,12 +12411,20 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
* dropping 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))
- changed = true;
+ if (cmdcon->alterEnforceability)
+ {
+ if (currcon->contype == CONSTRAINT_FOREIGN)
+ changed = ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
+ currcon->conrelid,
+ currcon->confrelid,
+ contuple, lockmode,
+ InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid);
+ else if (currcon->contype == CONSTRAINT_CHECK)
+ changed = ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel,
+ contuple, recurse, false,
+ lockmode);
+ }
else if (cmdcon->alterDeferrability &&
ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12585,6 +12603,163 @@ 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 caller, 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. We only need do this 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 = palloc0_object(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 a6fa9cacb72..0ed817f0c2c 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -390,6 +390,84 @@ 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;
+drop view check_constraint_status;
--
-- Primary keys
--
@@ -746,8 +824,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 b7f6efdd814..483c1e98372 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -266,6 +266,58 @@ 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;
+drop view check_constraint_status;
+
--
-- 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
