Subject: alter check constraint enforceability

From: jian he <[email-masked]>
Date: Mon, 2 Jun 2025 21:57:33 +0800

hi.

Currently in pg18, we can add not enforced check constraints.
but we can not do  ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
for check constraint.

The attached patch is implementation of changing enforceability of
check constraint.
From 486cf69121b0ea37a5c050bf1ceaa67196acd682 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 2 Jun 2025 21:54:53 +0800
Subject: [PATCH v1 1/1] alter check constraint enforceability

context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570

discussion: https://postgr.es/m/
---
 doc/src/sgml/ref/alter_table.sgml         |   4 +-
 src/backend/commands/tablecmds.c          | 190 ++++++++++++++++++++--
 src/test/regress/expected/constraints.out |  54 ++++++
 src/test/regress/expected/inherit.out     |  63 +++++++
 src/test/regress/sql/constraints.sql      |  37 +++++
 src/test/regress/sql/inherit.sql          |  45 +++++
 6 files changed, 375 insertions(+), 18 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index d63f3a621ac..afb7b04aa69 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -559,8 +559,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 acf11e83c04..9c1466041f9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -403,6 +403,10 @@ static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cm
 											Oid ReferencedParentUpdTrigger,
 											Oid ReferencingParentInsTrigger,
 											Oid ReferencingParentUpdTrigger);
+static bool ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+												 Relation conrel, Oid conrelid,
+												 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,
@@ -421,6 +425,9 @@ static void AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *c
 											 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,
@@ -431,7 +438,7 @@ static ObjectAddress ATExecValidateConstraint(List **wqueue,
 											  Relation rel, char *constrName,
 											  bool recurse, bool recursing, LOCKMODE lockmode);
 static void QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
-										HeapTuple contuple, LOCKMODE lockmode);
+										HeapTuple contuple, LOCKMODE lockmode, bool recursing);
 static void QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 										   char *constrName, HeapTuple contuple,
 										   bool recurse, bool recursing, LOCKMODE lockmode);
@@ -11867,7 +11874,7 @@ AttachPartitionForeignKey(List **wqueue,
 
 		/* Use the same lock as for AT_ValidateConstraint */
 		QueueFKConstraintValidation(wqueue, conrel, partition, partcontup,
-									ShareUpdateExclusiveLock);
+									ShareUpdateExclusiveLock, false);
 		ReleaseSysCache(partcontup);
 		table_close(conrel, RowExclusiveLock);
 	}
@@ -12151,7 +12158,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.
@@ -12213,11 +12220,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, check constraint can change enforceability"));
 	if (cmdcon->alterInheritability &&
 		currcon->contype != CONSTRAINT_NOTNULL)
 		ereport(ERROR,
@@ -12319,16 +12328,21 @@ 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 &&
+	if (cmdcon->alterEnforceability && currcon->contype == CONSTRAINT_FOREIGN &&
 		ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
 										currcon->conrelid, currcon->confrelid,
 										contuple, lockmode, InvalidOid,
 										InvalidOid, InvalidOid, InvalidOid))
 		changed = true;
+	else if (cmdcon->alterEnforceability && currcon->contype == CONSTRAINT_CHECK &&
+		ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel,
+											 currcon->conrelid,
+											 contuple, recurse, false, lockmode))
+		changed = true;
 
 	else if (cmdcon->alterDeferrability &&
 			 ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12359,7 +12373,148 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 }
 
 /*
- * Returns true if the constraint's enforceability is altered.
+ * Returns true if the CHECK constraint's enforceability is altered.
+ *
+ * Note that we must recurse even when trying to change a 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.
+ */
+static bool
+ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+									 Relation conrel, Oid conrelid,
+									 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);
+
+	rel = table_open(currcon->conrelid, lockmode);
+
+	/*
+	 * 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);
+
+	/*
+	 * For CHECK constraints, we must ensure that we only mark the constraint as
+	 * enforced on the parent if it's already enforced on the children.
+	 *
+	 * We recurse before changing enforceability  on the parent, to reduce risk
+	 * of deadlocks.
+	 */
+	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 changing constraint enforceability on the
+		 * parent unless we have chaned enforceability for all child tables.
+		 */
+		if (!recurse)
+			ereport(ERROR,
+					errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+					errmsg("constraint must be altered on child tables too"));
+
+		AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, childoid, false, true, lockmode);
+	}
+
+	/*
+	 * Tell Phase 3 to check that the constraint is satisfied by existing rows.
+	 * We do this only when alter the constraint from not enforced to enforced.
+	 */
+	if (rel->rd_rel->relkind == RELKIND_RELATION &&
+		cmdcon->is_enforced &&
+		!currcon->conenforced)
+	{
+		AlteredTableInfo *tab;
+		NewConstraint *newcon;
+		Datum		val;
+		char	   *conbin;
+
+		newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+		newcon->name = pstrdup(NameStr(currcon->conname));
+		newcon->contype = CONSTR_CHECK;
+		newcon->refrelid = InvalidOid;
+		newcon->refindid = InvalidOid;
+		newcon->conid = currcon->oid;
+
+		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);
+	}
+
+	if (currcon->conenforced != cmdcon->is_enforced)
+	{
+		AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple);
+		changed = true;
+	}
+
+	table_close(rel, NoLock);
+	return changed;
+}
+
+
+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,
+										 conrelid, childtup, recurse, recursing, lockmode);
+
+	systable_endscan(pscan);
+}
+
+/*
+ * 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.
@@ -12919,7 +13074,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
 	{
 		if (con->contype == CONSTRAINT_FOREIGN)
 		{
-			QueueFKConstraintValidation(wqueue, conrel, rel, tuple, lockmode);
+			QueueFKConstraintValidation(wqueue, conrel, rel, tuple, lockmode, false);
 		}
 		else if (con->contype == CONSTRAINT_CHECK)
 		{
@@ -12953,7 +13108,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
  */
 static void
 QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
-							HeapTuple contuple, LOCKMODE lockmode)
+							HeapTuple contuple, LOCKMODE lockmode, bool recursing)
 {
 	Form_pg_constraint con;
 	AlteredTableInfo *tab;
@@ -12983,8 +13138,11 @@ QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 		newcon->qual = (Node *) fkconstraint;
 
 		/* Find or create work queue entry for this table */
-		tab = ATGetQueueEntry(wqueue, rel);
-		tab->constraints = lappend(tab->constraints, newcon);
+		if (!recursing)
+		{
+			tab = ATGetQueueEntry(wqueue, rel);
+			tab->constraints = lappend(tab->constraints, newcon);
+		}
 	}
 
 	/*
@@ -13024,7 +13182,7 @@ QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 			childrel = table_open(childcon->conrelid, lockmode);
 
 			QueueFKConstraintValidation(wqueue, conrel, childrel, childtup,
-										lockmode);
+										lockmode, true);
 			table_close(childrel, NoLock);
 		}
 
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index ad6aaab7385..8e25ca4858c 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -390,6 +390,58 @@ SELECT * FROM COPY_TBL;
  6 | OK            | 4
 (2 rows)
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NO] ENFORCED
+create table parted_ch_tbl(a int, constraint cc check (a > 10) not enforced, b int ) partition by range(a);
+create table parted_ch_tbl_1 partition of parted_ch_tbl for values from (0) to (10) partition by list(b);
+create table parted_ch_tbl_11 partition of parted_ch_tbl_1 for values in (0, 1);
+create table parted_ch_tbl_12 partition of parted_ch_tbl_1 for values in (2);
+create table parted_ch_tbl_2 partition of parted_ch_tbl for values from (10) to (20);
+alter table parted_ch_tbl_2 add constraint cc_2 check( a < 15) not enforced;
+insert into parted_ch_tbl values (1, 2), (9, 1), (16, 16);
+alter table parted_ch_tbl alter constraint cc enforced; --error
+ERROR:  check constraint "cc" of relation "parted_ch_tbl_11" is violated by some row
+delete from parted_ch_tbl where a = 1;
+alter table parted_ch_tbl alter constraint cc enforced; --error
+ERROR:  check constraint "cc" of relation "parted_ch_tbl_11" is violated by some row
+delete from parted_ch_tbl where a = 9;
+alter table parted_ch_tbl alter constraint cc enforced; --ok
+--check these CHECK constraint status
+select  conname, conrelid::regclass, conenforced, convalidated
+from    pg_constraint
+where   conrelid::regclass::text ~* '^parted_ch' and conname = 'cc'
+order by conrelid::regclass, conname;
+ conname |     conrelid     | conenforced | convalidated 
+---------+------------------+-------------+--------------
+ cc      | parted_ch_tbl    | t           | t
+ cc      | parted_ch_tbl_1  | t           | t
+ cc      | parted_ch_tbl_11 | t           | t
+ cc      | parted_ch_tbl_12 | t           | t
+ cc      | parted_ch_tbl_2  | t           | t
+(5 rows)
+
+alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --error
+ERROR:  check constraint "cc_2" of relation "parted_ch_tbl_2" is violated by some row
+delete from parted_ch_tbl where a = 16;
+alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --ok
+alter table parted_ch_tbl alter constraint cc not enforced; --ok
+--check these CHECK constraint status
+select  conname, conrelid::regclass, conenforced, convalidated
+from    pg_constraint
+where   conrelid::regclass::text ~* '^parted_ch' and contype = 'c'
+order by conrelid::regclass, conname;
+ conname |     conrelid     | conenforced | convalidated 
+---------+------------------+-------------+--------------
+ cc      | parted_ch_tbl    | f           | f
+ cc      | parted_ch_tbl_1  | f           | f
+ cc      | parted_ch_tbl_11 | f           | f
+ cc      | parted_ch_tbl_12 | f           | f
+ cc      | parted_ch_tbl_2  | f           | f
+ cc_2    | parted_ch_tbl_2  | t           | t
+(6 rows)
+
+drop table parted_ch_tbl;
 --
 -- Primary keys
 --
@@ -746,8 +798,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, check constraint 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, check constraint can change enforceability
 DROP TABLE unique_tbl;
 --
 -- EXCLUDE constraints
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index f9b0c415cfd..a24fe7962d5 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1418,11 +1418,55 @@ order by 1, 2;
  p1_c3   | inh_check_constraint9  | f          |           2 | t           | t
 (38 rows)
 
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  constraint must be altered on child tables too
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+ERROR:  constraint must be altered on child tables too
+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;
+        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
+--ALTER TABLE ALTER CONSTRAINT
+--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);
+insert into p1 values(-1);
+alter table p1 alter constraint p1_a_check not enforced; --ok
+alter table p1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1" is violated by some row
+delete from only p1 where f1 = -1;
+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
@@ -1431,6 +1475,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;
+  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 337baab7ced..4a540120005 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -266,6 +266,43 @@ COPY COPY_TBL FROM :'filename';
 
 SELECT * FROM COPY_TBL;
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NO] ENFORCED
+create table parted_ch_tbl(a int, constraint cc check (a > 10) not enforced, b int ) partition by range(a);
+create table parted_ch_tbl_1 partition of parted_ch_tbl for values from (0) to (10) partition by list(b);
+create table parted_ch_tbl_11 partition of parted_ch_tbl_1 for values in (0, 1);
+create table parted_ch_tbl_12 partition of parted_ch_tbl_1 for values in (2);
+create table parted_ch_tbl_2 partition of parted_ch_tbl for values from (10) to (20);
+
+alter table parted_ch_tbl_2 add constraint cc_2 check( a < 15) not enforced;
+insert into parted_ch_tbl values (1, 2), (9, 1), (16, 16);
+
+alter table parted_ch_tbl alter constraint cc enforced; --error
+delete from parted_ch_tbl where a = 1;
+alter table parted_ch_tbl alter constraint cc enforced; --error
+delete from parted_ch_tbl where a = 9;
+alter table parted_ch_tbl alter constraint cc enforced; --ok
+
+--check these CHECK constraint status
+select  conname, conrelid::regclass, conenforced, convalidated
+from    pg_constraint
+where   conrelid::regclass::text ~* '^parted_ch' and conname = 'cc'
+order by conrelid::regclass, conname;
+
+alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --error
+delete from parted_ch_tbl where a = 16;
+alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --ok
+
+alter table parted_ch_tbl alter constraint cc not enforced; --ok
+--check these CHECK constraint status
+select  conname, conrelid::regclass, conenforced, convalidated
+from    pg_constraint
+where   conrelid::regclass::text ~* '^parted_ch' and contype = 'c'
+order by conrelid::regclass, conname;
+
+drop table parted_ch_tbl;
+
 --
 -- Primary keys
 --
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..0f505eadc85 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -510,6 +510,40 @@ select conrelid::regclass::text as relname, conname, conislocal, coninhcount, co
 from pg_constraint where conname like 'inh\_check\_constraint%'
 order by 1, 2;
 
+
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+
+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;
+drop table p1 cascade;
+
+--ALTER TABLE ALTER CONSTRAINT
+--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);
+insert into p1 values(-1);
+alter table p1 alter constraint p1_a_check not enforced; --ok
+alter table p1 alter constraint p1_a_check enforced; --error
+delete from only p1 where f1 = -1;
+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 +554,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;
 drop table p1 cascade;
 
 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
-- 
2.34.1

---

From: jian he <[email-masked]>
Date: Fri, 4 Jul 2025 19:59:47 +0800

> The attached patch is implementation of changing enforceability of

hi.
rebase and minor refactoring.
From ba604766c43e474aa6b08ba613dfb04854ec7f40 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Fri, 4 Jul 2025 19:51:43 +0800
Subject: [PATCH v2 1/1] alter check constraint enforceability

syntax: ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED

context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/alter_table.sgml         |   4 +-
 src/backend/commands/tablecmds.c          | 164 ++++++++++++++++++++--
 src/test/regress/expected/constraints.out |  55 ++++++++
 src/test/regress/expected/inherit.out     |  58 ++++++++
 src/test/regress/sql/constraints.sql      |  36 +++++
 src/test/regress/sql/inherit.sql          |  40 ++++++
 6 files changed, 346 insertions(+), 11 deletions(-)

index 1e4f26c13f6..5f6504711c7 100644
index cb811520c29..5ffbb9d6b5a 100644
@@ -403,6 +403,9 @@ static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cm
+												 Relation conrel, HeapTuple contuple,
@@ -421,6 +424,9 @@ static void AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *c
@@ -12153,7 +12159,7 @@ GetForeignKeyCheckTriggers(Relation trigrel,
@@ -12215,11 +12221,13 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
@@ -12321,16 +12329,20 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
+		ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel, contuple,
+											 recurse, false, lockmode))
+		changed = true;
 
 	else if (cmdcon->alterDeferrability &&
 			 ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12361,7 +12373,141 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 }
 
 /*
- * Returns true if the constraint's enforceability is altered.
+ * Returns true if the CHECK constraint's enforceability is altered.
+ *
+ * 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.
+ */
+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);
+
+	rel = table_open(currcon->conrelid, lockmode);
+	if (currcon->conenforced != cmdcon->is_enforced)
+	{
+		AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple);
+		changed = true;
+	}
+
+	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 changing constraint enforceability on
+			 * the parent unless we have chaned enforceability for all child
+			 * tables.
+			*/
+			if (!recurse)
+				ereport(ERROR,
+						errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						errmsg("constraint must be altered on child tables too"));
+
+			AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, childoid, false, true, lockmode);
+		}
+	}
+
+	/*
+	 * Tell Phase 3 to check that the constraint is satisfied by existing rows.
+	 * We do this only when alter the constraint from not enforced to enforced.
+	 */
+	if (rel->rd_rel->relkind == RELKIND_RELATION &&
+		cmdcon->is_enforced &&
+		!currcon->conenforced)
+	{
+		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;
+}
+
+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 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.
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index ccea883cffd..10075546034 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -390,6 +390,59 @@ SELECT * FROM COPY_TBL;
  6 | OK            | 4
 (2 rows)
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+create table parted_ch_tbl(a int, constraint cc check (a > 10) not enforced, b int ) partition by range(a);
+create table parted_ch_tbl_1 partition of parted_ch_tbl for values from (0) to (10) partition by list(b);
+create table parted_ch_tbl_11 partition of parted_ch_tbl_1 for values in (0, 1);
+create table parted_ch_tbl_12 partition of parted_ch_tbl_1 for values in (2);
+create table parted_ch_tbl_2 partition of parted_ch_tbl for values from (10) to (20);
+alter table parted_ch_tbl_2 add constraint cc_2 check( a < 15) not enforced;
+insert into parted_ch_tbl values (1, 2), (9, 1), (16, 16);
+create 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 conrelid::regclass, conname;
+alter table parted_ch_tbl alter constraint cc not enforced; --no-op
+alter table parted_ch_tbl alter constraint cc enforced; --error
+ERROR:  check constraint "cc" of relation "parted_ch_tbl_11" is violated by some row
+delete from parted_ch_tbl where a = 1;
+alter table parted_ch_tbl alter constraint cc enforced; --error
+ERROR:  check constraint "cc" of relation "parted_ch_tbl_11" is violated by some row
+delete from parted_ch_tbl where a = 9;
+alter table parted_ch_tbl alter constraint cc enforced; --ok
+--check these CHECK constraint status
+select * from check_constraint_status;
+ conname |     conrelid     | conenforced | convalidated 
+---------+------------------+-------------+--------------
+ cc      | parted_ch_tbl    | t           | t
+ cc      | parted_ch_tbl_1  | t           | t
+ cc      | parted_ch_tbl_11 | t           | t
+ cc      | parted_ch_tbl_12 | t           | t
+ cc      | parted_ch_tbl_2  | t           | t
+ cc_2    | parted_ch_tbl_2  | f           | f
+(6 rows)
+
+alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --error
+ERROR:  check constraint "cc_2" of relation "parted_ch_tbl_2" is violated by some row
+delete from parted_ch_tbl where a = 16;
+alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --ok
+alter table parted_ch_tbl alter constraint cc not enforced; --ok
+--check these CHECK constraint status again
+select * from check_constraint_status;
+ conname |     conrelid     | conenforced | convalidated 
+---------+------------------+-------------+--------------
+ cc      | parted_ch_tbl    | f           | f
+ cc      | parted_ch_tbl_1  | f           | f
+ cc      | parted_ch_tbl_11 | f           | f
+ cc      | parted_ch_tbl_12 | f           | f
+ cc      | parted_ch_tbl_2  | f           | f
+ cc_2    | parted_ch_tbl_2  | t           | t
+(6 rows)
+
+drop table parted_ch_tbl;
 --
 -- Primary keys
 --
@@ -746,8 +799,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, check constraint 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, check constraint 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 78dead65325..92321efb2d4 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1418,11 +1418,50 @@ order by 1, 2;
  p1_c3   | inh_check_constraint9  | f          |           2 | t           | t
 (38 rows)
 
+-- Tests for ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
@@ -1431,6 +1470,25 @@ drop cascades to table p1_c3
index 7487723ab84..c9134c91d21 100644
@@ -266,6 +266,42 @@ COPY COPY_TBL FROM :'filename';
 
 SELECT * FROM COPY_TBL;
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+create table parted_ch_tbl(a int, constraint cc check (a > 10) not enforced, b int ) partition by range(a);
+create table parted_ch_tbl_1 partition of parted_ch_tbl for values from (0) to (10) partition by list(b);
+create table parted_ch_tbl_11 partition of parted_ch_tbl_1 for values in (0, 1);
+create table parted_ch_tbl_12 partition of parted_ch_tbl_1 for values in (2);
+create table parted_ch_tbl_2 partition of parted_ch_tbl for values from (10) to (20);
+alter table parted_ch_tbl_2 add constraint cc_2 check( a < 15) not enforced;
+insert into parted_ch_tbl values (1, 2), (9, 1), (16, 16);
+
+create 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 conrelid::regclass, conname;
+
+alter table parted_ch_tbl alter constraint cc not enforced; --no-op
+alter table parted_ch_tbl alter constraint cc enforced; --error
+delete from parted_ch_tbl where a = 1;
+alter table parted_ch_tbl alter constraint cc enforced; --error
+delete from parted_ch_tbl where a = 9;
+alter table parted_ch_tbl alter constraint cc enforced; --ok
+
+--check these CHECK constraint status
+select * from check_constraint_status;
+
+alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --error
+delete from parted_ch_tbl where a = 16;
+alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --ok
+alter table parted_ch_tbl alter constraint cc not enforced; --ok
+
+--check these CHECK constraint status again
+select * from check_constraint_status;
+drop table parted_ch_tbl;
+
 --
 -- Primary keys
 --
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..d8040b36da5 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -510,6 +510,35 @@ select conrelid::regclass::text as relname, conname, conislocal, coninhcount, co
 from pg_constraint where conname like 'inh\_check\_constraint%'
 order by 1, 2;
 
+-- Tests for ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
 
 --
@@ -520,6 +549,17 @@ drop table p1 cascade;

---

From: Robert Treat <[email-masked]>
Date: Wed, 6 Aug 2025 19:35:08 -0400

> > The attached patch is implementation of changing enforceability of

Initial look and testing looks good. There are some odd parts to work
through with partitioned tables and recursion (for example, if you
have a parent unenforced, and a child enforced, setting a parent
enforced and then not enforced will recurse to the child, so you end
up in a different state. that could be surprising, but the alternative
is not obviously more sensicle).

Some minor items below:

"Only foreign key and check constraints can change enforceability"

--

+ /*

* tables, because we can't change constraint enforceability on
* the parent unless we have changed enforceability for all child

-- 

i think I have convinced myself that this is correct, but maybe I will
ask you if you had any concerns that this needed to also consider
RELKIND_PARTITIONED_TABLE as well?

Robert Treat
https://xzilla.net

---

From: jian he <[email-masked]>
Date: Mon, 11 Aug 2025 17:53:08 +0800

> RELKIND_PARTITIONED_TABLE as well?

ATExecAlterCheckConstrEnforceability itself will be recursive to all
the children.
AlterConstrUpdateConstraintEntry is responsible for changing the catalog state.
except the changing the catalog state, if we change the check
constraint from NOT ENFORCED
to ENFORCED,  we also need to verify it in phase 3.
that's the purpose of
> + if (rel->rd_rel->relkind == RELKIND_RELATION &&

partitioned tables don't have storage, phase3 table scan to verify
check constraint on partitioned table
don't have effect.

also partitioned table check constraint (name, definition
(pg_constraint.conbin) must match with partition
otherwise partition can be attached to the partitioned table.
so here you don't need to consider RELKIND_PARTITIONED_TABLE.
From a54609c8aa66174e171fdfd7c08519e587561d85 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 11 Aug 2025 17:51:56 +0800
Subject: [PATCH v3 1/1] alter check constraint enforceability

context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/alter_table.sgml         |   4 +-
 src/backend/commands/tablecmds.c          | 163 ++++++++++++++++++++--
 src/test/regress/expected/constraints.out |  55 ++++++++
 src/test/regress/expected/inherit.out     |  58 ++++++++
 src/test/regress/sql/constraints.sql      |  36 +++++
 src/test/regress/sql/inherit.sql          |  40 ++++++
 6 files changed, 345 insertions(+), 11 deletions(-)

index 8867da6c693..a03e36762f2 100644
@@ -571,8 +571,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
index c6dd2e020da..a3fd306c26b 100644
+				errhint("Only foreign key and check constraints can change enforceability"));
 
 	else if (cmdcon->alterDeferrability &&
 			 ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12361,7 +12373,140 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 }
 
 /*
+			 * tables, because we can't change constraint enforceability on the
+			 * parent unless we have changed enforceability for all child.
index 3590d3274f0..a48dd898c9f 100644
 
+--
+-- CHECK constraints
+HINT:  Only foreign key and check constraints can change enforceability
+HINT:  Only foreign key and check constraints can change enforceability
index 5b5055babdc..a4c80246ce1 100644
 
+order by conrelid::regclass::text collate "C";
+order by conrelid::regclass::text collate "C";
index 1f6dc8fd69f..e3201c9b3d3 100644
 
 SELECT * FROM COPY_TBL;
 
+--
+-- CHECK constraints
index 699e8ac09c8..a742a6abd36 100644
 
+order by conrelid::regclass::text collate "C";
 
+order by conrelid::regclass::text collate "C";
 drop table p1 cascade;

---

From: Kirill Reshke <[email-masked]>
Date: Mon, 11 Aug 2025 18:59:53 +0500

> so here you don't need to consider RELKIND_PARTITIONED_TABLE.

Hi!
I looked at v3.

Should we rename `ATExecAlterConstrEnforceability` to
`ATExecAlterFKConstrEnforceability `?

-- 
Best regards,
Kirill Reshke

---

From: Robert Treat <[email-masked]>
Date: Thu, 6 Nov 2025 18:29:28 -0500

> `ATExecAlterFKConstrEnforceability `?

+1

Robert Treat
https://xzilla.net

---

From: jian he <[email-masked]>
Date: Thu, 4 Dec 2025 14:51:54 +0800

hi.

AlterConstrEnforceabilityRecurse renamed to
AlterFKConstrEnforceabilityRecurse

ATExecAlterConstrEnforceability renamed to
ATExecAlterFKConstrEnforceability.

There seem to be no tests for cases where a partitioned table’s check constraint
is not enforced, but the partition’s constraint is enforced. I’ve added tests
for this case.

ATExecAlterCheckConstrEnforceability
``rel = table_open(currcon->conrelid, NoLock);``

NoLock is ok, because parent is already locked, obviously,
``find_all_inheritors(RelationGetRelid(rel), lockmode, NULL); ``
will lock all the children with lockmode.

--
jian
https://www.enterprisedb.com
From 97e7ac0688464b637f539b7df43d84ff68109548 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Thu, 4 Dec 2025 14:39:44 +0800
Subject: [PATCH v4 1/1] alter check constraint enforceability

context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/alter_table.sgml         |   4 +-
 src/backend/commands/tablecmds.c          | 274 +++++++++++++++++-----
 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, 454 insertions(+), 59 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 9d23ad5a0fb..faa231f93e4 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 07e5b95782e..5be23ab95b2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -396,14 +396,17 @@ 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 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,
@@ -414,14 +417,17 @@ 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 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 +12187,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 +12249,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,
@@ -12349,17 +12357,27 @@ 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))
-		changed = true;
-
+	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;
+		}
+	}
 	else if (cmdcon->alterDeferrability &&
 			 ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
 											contuple, recurse, &otherrelids,
@@ -12389,7 +12407,149 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 }
 
 /*
- * Returns true if the constraint's enforceability is altered.
+ * Returns true if the CHECK constraint's enforceability is altered.
+ *
+ * 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.
+ *
+ * conrel is the pg_constraint catalog relation.
+ */
+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;
+	}
+
+	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;
+}
+
+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 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 +12561,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,7 +12604,7 @@ 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,
+			AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
 											 fkrelid, pkrelid, contuple,
 											 lockmode, InvalidOid, InvalidOid,
 											 InvalidOid, InvalidOid);
@@ -12523,7 +12683,7 @@ 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,
+			AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
 											 fkrelid, pkrelid, contuple,
 											 lockmode, ReferencedDelTriggerOid,
 											 ReferencedUpdTriggerOid,
@@ -12741,7 +12901,7 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
 }
 
 /*
- * Invokes ATExecAlterConstrEnforceability for each constraint that is a child of
+ * Invokes ATExecAlterFKConstrEnforceability for each constraint that is a child of
  * the specified constraint.
  *
  * Note that this doesn't handle recursion the normal way, viz. by scanning the
@@ -12749,17 +12909,17 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
  * 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 +12939,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);
 }
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 1bbf59cca02..1d206b8512f 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
+HINT:  Do not specify the ONLY keyword.
+HINT:  Do not specify the ONLY keyword.
@@ -1434,6 +1477,25 @@ drop cascades to table p1_c3
index 733a1dbccfe..514ef4b2a50 100644
@@ -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
 
 --
@@ -520,6 +552,17 @@ drop table p1 cascade;

---

From: Amul Sul <[email-masked]>
Date: Mon, 8 Dec 2025 15:28:11 +0530

> On Fri, Nov 7, 2025 at 7:29 AM Robert Treat <[email-masked]> 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.
--

+   /*

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.
--

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.
--

Not sure why we need to mention conrel here only?
--

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?
--

+static void

Kindly add a prolog comment.

Regards,
Amul

---

From: jian he <[email-masked]>
Date: Fri, 12 Dec 2025 15:54:55 +0800

> restriction all the places, it is better to adhere to it as much as possible.

hi.
I have addressed all your points mentioned above.

> or not, or simply move the relevant note from the prolog here?

moving the prolog to the IF check seems easier.

> 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-masked]>
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/[email-masked]
---
 src/backend/commands/tablecmds.c | 117 ++++++++++++++++---------------
 1 file changed, 59 insertions(+), 58 deletions(-)

index 1c9ef53be20..752634018c2 100644
@@ -396,14 +396,14 @@ static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel,
@@ -414,14 +414,14 @@ static bool ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cm
@@ -12349,15 +12349,15 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 	if (cmdcon->alterEnforceability &&
+										  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,
 }
 
 /*
- * the specified constraint.
+ * Invokes ATExecAlterFKConstrEnforceability for each foreign key constraint
+ * that is a child of the specified constraint.
  * list of child relations and recursing; instead it uses the conparentid
@@ -12779,12 +12780,12 @@ AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 							   true, NULL, 1, &pkey);

 	systable_endscan(pscan);
 }
-- 
2.34.1

From 8c760261cc71bbd584dc2b6e9ca418e02f5cd6c8 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Thu, 11 Dec 2025 14:44:23 +0800
Subject: [PATCH v5 2/2] alter check constraint enforceability

commitfest: https://commitfest.postgresql.org/patch/5796
context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
discussion: https://postgr.es/m/[email-masked]
---
 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;
 }
 
+ /*
+	 * Parent relation already locked by called, children will be locked by
+	 * 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.
+			AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel,
+												  childoid, false, true,
+												  lockmode);
+ * 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.
  * Returns true if the constraint's deferrability is altered.
index 1bbf59cca02..c0cf19d0eb0 100644
 
+--
+-- CHECK constraints
+HINT:  Only foreign key and check constraints can change enforceability.
+HINT:  Only foreign key and check constraints can change enforceability.
 
+--
+-- CHECK constraints
 
 SELECT * FROM COPY_TBL;
 
+--
+-- CHECK constraints
 
+--
+-- CHECK constraints

---

From: Amul Sul <[email-masked]>
Date: Mon, 15 Dec 2025 17:19:00 +0530

On Fri, Dec 12, 2025 at 1:25 PM jian he <[email-masked]> wrote:
>>  [....]
> AlterConstrEnforceabilityRecurse renamed to AlterFKConstrEnforceabilityRecurse n

Looks good to me, thanks !

> v5-0002: alter check constraint enforceability

The patch also looks good, but I have a minor comment for the test --
you created the check_constraint_status view, which is not dropped, it
should be dropped at the end. Also, instead of a view, I think you
could use the \set psql-meta-command; for example, see the
init_range_parted or show_data tests in update.sql

Also, run pgindent on both patches.

Regards,
Amul

---

From: jian he <[email-masked]>
Date: Tue, 16 Dec 2025 12:55:52 +0800

> Also, run pgindent on both patches.

i have tried using \set, but it seems to require the query within a single line.
since the view check_constraint_status definition is quite longer, \set would
make it less readable, so I choose to use view.

previously I use
+ newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
now use
+        newcon = palloc0_object(NewConstraint);

v6-0001, v6-0002 both indented properly via pgindent, also polished the commit
messages.

--
jian
https://www.enterprisedb.com/
From 93d679d9f2b9fe7bdbec511d19211158312ff4f3 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 16 Dec 2025 12:28:46 +0800
Subject: [PATCH v6 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-masked]>
Reviewed-by: Amul Sul <[email-masked]>
Reviewed-by: Kirill Reshke <[email-masked]>,
Reviewed-by: Robert Treat <[email-masked]>

(XXX delete this line later) commitfest: https://commitfest.postgresql.org/patch/5796
discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c | 119 ++++++++++++++++---------------
 1 file changed, 60 insertions(+), 59 deletions(-)

index 6b1a00ed477..dc906a16489 100644
@@ -12385,15 +12385,15 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
-	 * automatically.
+	 * Modifying foreign key enforceability involves either creating or
+	 * dropping the trigger, during which the deferrability setting will be
+	 * adjusted automatically.
 
 	else if (cmdcon->alterDeferrability &&
@@ -12425,7 +12425,7 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 }
 
 /*
@@ -12437,14 +12437,14 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
@@ -12480,10 +12480,10 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 
@@ -12559,12 +12559,13 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 
 	table_close(rel, NoLock);
@@ -12777,25 +12778,25 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
 }
 
 /*
@@ -12815,12 +12816,12 @@ AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 							   true, NULL, 1, &pkey);

 	systable_endscan(pscan);
 }
-- 
2.34.1

From ba246abdb3df94e3e4f44d4301596890f59d413f Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 16 Dec 2025 12:47:41 +0800
Subject: [PATCH v6 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-masked]>
Reviewed-by: Amul Sul <[email-masked]>
Reviewed-by: Kirill Reshke <[email-masked]>,
Reviewed-by: Robert Treat <[email-masked]>

(XXX delete this line later) commitfest: https://commitfest.postgresql.org/patch/5796
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/alter_table.sgml         |   4 +-
 src/backend/commands/tablecmds.c          | 192 +++++++++++++++++++++-
 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(+), 10 deletions(-)

index 9abd8037f28..58fcbfb33c0 100644
@@ -575,8 +575,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
index dc906a16489..5fb5fa6c111 100644
@@ -12217,7 +12225,7 @@ GetForeignKeyCheckTriggers(Relation trigrel,
@@ -12279,11 +12287,13 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
 				 errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"",
+				 errhint("Only foreign key and check constraints can change enforceability.")));
@@ -12389,12 +12399,21 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 	 * dropping the trigger, during which the deferrability setting will be
 	 * adjusted automatically.
 
 	else if (cmdcon->alterDeferrability &&
 			 ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12573,6 +12592,163 @@ ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 	return changed;
 }
 
+ /*
+	 * Parent relation already locked by caller, children will be locked by
+	 * 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.
+		 * Also, if the constraint is a NO INHERIT constraint, we shouldn't
+		 * try to look for it in the children.
+			 * 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.
+	 * 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.
+		newcon = palloc0_object(NewConstraint);
index 1bbf59cca02..3850e9925c0 100644
@@ -390,6 +390,84 @@ SELECT * FROM COPY_TBL;
  6 | OK            | 4
 (2 rows)
 
+--
+-- CHECK constraints
+drop view check_constraint_status;
@@ -746,8 +824,10 @@ LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
 
+--
+-- CHECK constraints
index 733a1dbccfe..9609a67dda0 100644
@@ -266,6 +266,58 @@ COPY COPY_TBL FROM :'filename';
 
 SELECT * FROM COPY_TBL;
 
+--
+-- CHECK constraints
+drop view check_constraint_status;
 
+--
+-- CHECK constraints

---

From: Amul Sul <[email-masked]>
Date: Tue, 16 Dec 2025 12:04:40 +0530

On Tue, Dec 16, 2025 at 10:26 AM jian he <[email-masked]> wrote:
> >  [...]

Thanks. I don't have any other comments. The patch is now ready for
committer review. I have updated the status of the CommitFest entry
accordingly.

Regards,
Amul

---

From: Andrew Dunstan <[email-masked]>
Date: Wed, 11 Mar 2026 16:17:09 -0400

On 2026-02-21 Sa 9:42 AM, jian he wrote:
> On Wed, Feb 11, 2026 at 3:37 AM Zsolt Parragi <[email-masked]> wrote:
>> Hello
>>
>> Isn't this a behavior change?
>>
> Hi.
> You are right, I missed this minor detail.
> Now, I changed it to
>
> +            changed = ATExecAlterFKConstrEnforceability(wqueue,
> cmdcon, conrel, tgrel,
> +                                                        currcon->conrelid,
> +                                                        currcon->confrelid,
> +                                                        contuple, lockmode,
> +            changed = ATExecAlterCheckConstrEnforceability(wqueue,
> cmdcon, conrel,
> +                                                           contuple,
> recurse, false,
> +                                                           lockmode);
> +    }
>
>
>

pushed with minor tweaks.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com