alter check constraint enforceability

From ba604766c43e474aa6b08ba613dfb04854ec7f40 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com
---
 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(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1e4f26c13f6..5f6504711c7 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 cb811520c29..5ffbb9d6b5a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -403,6 +403,9 @@ static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cm
 											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,
@@ -421,6 +424,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,
@@ -12153,7 +12159,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.
@@ -12215,11 +12221,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,
@@ -12321,16 +12329,20 @@ 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, 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
+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
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1_c1" is violated by some row
+drop table p1 cascade;
+NOTICE:  drop cascades to table p1_c1
 --
 -- Similarly, check the merging of existing constraints; a parent constraint
 -- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
@@ -1431,6 +1470,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 7487723ab84..c9134c91d21 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -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
+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;
+
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
 drop table p1 cascade;
 
 --
@@ -520,6 +549,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 a54609c8aa66174e171fdfd7c08519e587561d85 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 11 Aug 2025 17:51:56 +0800
Subject: [PATCH v3 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/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com
---
 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(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8867da6c693..a03e36762f2 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -571,8 +571,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 c6dd2e020da..a3fd306c26b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -403,6 +403,9 @@ static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cm
 											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,
@@ -421,6 +424,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,
@@ -12153,7 +12159,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.
@@ -12215,11 +12221,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,
@@ -12321,16 +12329,20 @@ 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, contuple,
+											 recurse, false, lockmode))
+		changed = true;
 
 	else if (cmdcon->alterDeferrability &&
 			 ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12361,7 +12373,140 @@ 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 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"));
+
+			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 3590d3274f0..a48dd898c9f 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 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 5b5055babdc..a4c80246ce1 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
+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::text collate "C";
+        conname        | conenforced | convalidated | conrelid 
+-----------------------+-------------+--------------+----------
+ inh_check_constraint3 | f           | f            | p1
+ inh_check_constraint3 | f           | f            | p1_c1
+ inh_check_constraint3 | f           | f            | p1_c2
+ inh_check_constraint3 | f           | f            | p1_c3
+(4 rows)
+
 drop table p1 cascade;
 NOTICE:  drop cascades to 3 other objects
 DETAIL:  drop cascades to table p1_c1
 drop cascades to table p1_c2
 drop cascades to table p1_c3
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1_c1" is violated by some row
+drop table p1 cascade;
+NOTICE:  drop cascades to table p1_c1
 --
 -- Similarly, check the merging of existing constraints; a parent constraint
 -- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
@@ -1431,6 +1470,25 @@ drop cascades to table p1_c3
 create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
 alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+ERROR:  new row for relation "p1_c1" violates check constraint "p1_a_check"
+DETAIL:  Failing row contains (-1).
+alter table p1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1" is violated by some row
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1 alter constraint p1_a_check not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+  conname   | conenforced | convalidated | conrelid 
+------------+-------------+--------------+----------
+ p1_a_check | f           | f            | p1
+ p1_a_check | f           | f            | p1_c1
+(2 rows)
+
 drop table p1 cascade;
 NOTICE:  drop cascades to table p1_c1
 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 1f6dc8fd69f..e3201c9b3d3 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -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..a742a6abd36 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
+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::text collate "C";
+drop table p1 cascade;
+
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
 drop table p1 cascade;
 
 --
@@ -520,6 +549,17 @@ drop table p1 cascade;
 create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
 alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+alter table p1 alter constraint p1_a_check enforced; --error
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1 alter constraint p1_a_check not enforced; --ok
+
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
 drop table p1 cascade;
 
 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
-- 
2.34.1

From 97e7ac0688464b637f539b7df43d84ff68109548 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 4 Dec 2025 14:39:44 +0800
Subject: [PATCH v4 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/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com
---
 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
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  constraint must be altered on child tables too
+HINT:  Do not specify the ONLY keyword.
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+ERROR:  constraint must be altered on child tables too
+HINT:  Do not specify the ONLY keyword.
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+alter table p1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  check constraint "inh_check_constraint3" of relation "p1_c1" is violated by some row
+delete from only p1_c1 where f1 = -2;
+alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  check constraint "inh_check_constraint3" of relation "p1_c3" is violated by some row
+delete from only p1_c3 where f1 = -3;
+alter table p1 alter constraint inh_check_constraint3 enforced; --ok
+alter table p1 alter constraint inh_check_constraint3 not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'inh_check_constraint3' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+        conname        | conenforced | convalidated | conrelid 
+-----------------------+-------------+--------------+----------
+ inh_check_constraint3 | f           | f            | p1
+ inh_check_constraint3 | f           | f            | p1_c1
+ inh_check_constraint3 | f           | f            | p1_c2
+ inh_check_constraint3 | f           | f            | p1_c3
+(4 rows)
+
 drop table p1 cascade;
 NOTICE:  drop cascades to 3 other objects
 DETAIL:  drop cascades to table p1_c1
 drop cascades to table p1_c2
 drop cascades to table p1_c3
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1_c1" is violated by some row
+drop table p1 cascade;
+NOTICE:  drop cascades to table p1_c1
 --
 -- Similarly, check the merging of existing constraints; a parent constraint
 -- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
@@ -1434,6 +1477,25 @@ drop cascades to table p1_c3
 create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
 alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+ERROR:  new row for relation "p1_c1" violates check constraint "p1_a_check"
+DETAIL:  Failing row contains (-1).
+alter table p1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1" is violated by some row
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1 alter constraint p1_a_check not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+  conname   | conenforced | convalidated | conrelid 
+------------+-------------+--------------+----------
+ p1_a_check | f           | f            | p1
+ p1_a_check | f           | f            | p1_c1
+(2 rows)
+
 drop table p1 cascade;
 NOTICE:  drop cascades to table p1_c1
 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 733a1dbccfe..514ef4b2a50 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -266,6 +266,57 @@ COPY COPY_TBL FROM :'filename';
 
 SELECT * FROM COPY_TBL;
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+create table parted_ch(
+  a int, b int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) not enforced
+) partition by range(a);
+create table parted_ch_1 partition of parted_ch for values from (0) to (10) partition by list(b);
+create table parted_ch_11 partition of parted_ch_1 for values in (0, 1, 22,4);
+create table parted_ch_12 partition of parted_ch_1 for values in (2);
+create table parted_ch_2(b int, a int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) enforced,
+  constraint cc_2 check( a < 15) not enforced
+);
+alter table parted_ch attach partition parted_ch_2 for values from (10) to (20);
+
+insert into parted_ch values (1, 22), (9, 1), (16, 16);
+
+alter table parted_ch alter constraint cc_1 enforced; --error
+update parted_ch set b = 4 where b = 22;
+alter table parted_ch alter constraint cc_1 enforced; --ok
+
+create or replace view check_constraint_status as
+select  conname, conrelid::regclass, conenforced, convalidated
+from    pg_constraint
+where   conrelid::regclass::text ~* '^parted_ch' and contype = 'c'
+order by conname, conrelid::regclass::text collate "C";
+
+alter table parted_ch alter constraint cc not enforced; --no-op
+alter table parted_ch alter constraint cc enforced; --error
+delete from parted_ch where a = 1;
+alter table parted_ch alter constraint cc enforced; --error
+delete from parted_ch where a = 9;
+alter table parted_ch alter constraint cc enforced;
+
+--check these CHECK constraint status
+select * from check_constraint_status;
+
+alter table parted_ch_2 alter constraint cc_2 enforced; --error
+delete from parted_ch where a = 16;
+alter table parted_ch_2 alter constraint cc_2 enforced;
+alter table parted_ch_2 alter constraint cc not enforced;
+alter table parted_ch_2 alter constraint cc_1 not enforced;
+alter table parted_ch_2 alter constraint cc_2 not enforced;
+
+--check these CHECK constraint status again
+select * from check_constraint_status;
+drop table parted_ch;
+
 --
 -- Primary keys
 --
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..8f986904389 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -510,6 +510,38 @@ select conrelid::regclass::text as relname, conname, conislocal, coninhcount, co
 from pg_constraint where conname like 'inh\_check\_constraint%'
 order by 1, 2;
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+
+alter table p1 alter constraint inh_check_constraint3 enforced; --error
+delete from only p1_c1 where f1 = -2;
+alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error
+
+delete from only p1_c3 where f1 = -3;
+alter table p1 alter constraint inh_check_constraint3 enforced; --ok
+alter table p1 alter constraint inh_check_constraint3 not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'inh_check_constraint3' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+drop table p1 cascade;
+
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
 drop table p1 cascade;
 
 --
@@ -520,6 +552,17 @@ drop table p1 cascade;
 create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
 alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+alter table p1 alter constraint p1_a_check enforced; --error
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1 alter constraint p1_a_check not enforced; --ok
+
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
 drop table p1 cascade;
 
 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
-- 
2.34.1

From dd3ef63485d6a438ca3af1664696b9d265de91b3 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 11 Dec 2025 13:10:51 +0800
Subject: [PATCH v5 1/2] rename "AlterConstrEnforceability" to
 "AlterFKConstrEnforceability"

AlterConstrEnforceabilityRecurse renamed to AlterFKConstrEnforceabilityRecurse
ATExecAlterConstrEnforceability renamed to ATExecAlterFKConstrEnforceability.

commitfest: https://commitfest.postgresql.org/patch/5796
context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
discussion: https://postgr.es/m/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com
---
 src/backend/commands/tablecmds.c | 117 ++++++++++++++++---------------
 1 file changed, 59 insertions(+), 58 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1c9ef53be20..752634018c2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -396,14 +396,14 @@ static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel,
 static bool ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel,
 										  Relation tgrel, Relation rel, HeapTuple contuple,
 										  bool recurse, LOCKMODE lockmode);
-static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
-											Relation conrel, Relation tgrel,
-											Oid fkrelid, Oid pkrelid,
-											HeapTuple contuple, LOCKMODE lockmode,
-											Oid ReferencedParentDelTrigger,
-											Oid ReferencedParentUpdTrigger,
-											Oid ReferencingParentInsTrigger,
-											Oid ReferencingParentUpdTrigger);
+static bool ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+											  Relation conrel, Relation tgrel,
+											  Oid fkrelid, Oid pkrelid,
+											  HeapTuple contuple, LOCKMODE lockmode,
+											  Oid ReferencedParentDelTrigger,
+											  Oid ReferencedParentUpdTrigger,
+											  Oid ReferencingParentInsTrigger,
+											  Oid ReferencingParentUpdTrigger);
 static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon,
 										   Relation conrel, Relation tgrel, Relation rel,
 										   HeapTuple contuple, bool recurse,
@@ -414,14 +414,14 @@ static bool ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cm
 static void AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
 											bool deferrable, bool initdeferred,
 											List **otherrelids);
-static void AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
-											 Relation conrel, Relation tgrel,
-											 Oid fkrelid, Oid pkrelid,
-											 HeapTuple contuple, LOCKMODE lockmode,
-											 Oid ReferencedParentDelTrigger,
-											 Oid ReferencedParentUpdTrigger,
-											 Oid ReferencingParentInsTrigger,
-											 Oid ReferencingParentUpdTrigger);
+static void AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+											   Relation conrel, Relation tgrel,
+											   Oid fkrelid, Oid pkrelid,
+											   HeapTuple contuple, LOCKMODE lockmode,
+											   Oid ReferencedParentDelTrigger,
+											   Oid ReferencedParentUpdTrigger,
+											   Oid ReferencingParentInsTrigger,
+											   Oid ReferencingParentUpdTrigger);
 static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 											Relation conrel, Relation tgrel, Relation rel,
 											HeapTuple contuple, bool recurse,
@@ -12349,15 +12349,15 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 	 * enforceability, we don't need to explicitly update multiple entries in
 	 * pg_trigger related to deferrability.
 	 *
-	 * Modifying enforceability involves either creating or dropping the
-	 * trigger, during which the deferrability setting will be adjusted
+	 * Modifying foreign key enforceability involves either creating or dropping
+	 * the trigger, during which the deferrability setting will be adjusted
 	 * automatically.
 	 */
 	if (cmdcon->alterEnforceability &&
-		ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
-										currcon->conrelid, currcon->confrelid,
-										contuple, lockmode, InvalidOid,
-										InvalidOid, InvalidOid, InvalidOid))
+		ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
+										  currcon->conrelid, currcon->confrelid,
+										  contuple, lockmode, InvalidOid,
+										  InvalidOid, InvalidOid, InvalidOid))
 		changed = true;
 
 	else if (cmdcon->alterDeferrability &&
@@ -12389,7 +12389,7 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 }
 
 /*
- * Returns true if the constraint's enforceability is altered.
+ * Returns true if the foreign key constraint's enforceability is altered.
  *
  * Depending on whether the constraint is being set to ENFORCED or NOT
  * ENFORCED, it creates or drops the trigger accordingly.
@@ -12401,14 +12401,14 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
  * enforced, as descendant constraints cannot be different in that case.
  */
 static bool
-ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
-								Relation conrel, Relation tgrel,
-								Oid fkrelid, Oid pkrelid,
-								HeapTuple contuple, LOCKMODE lockmode,
-								Oid ReferencedParentDelTrigger,
-								Oid ReferencedParentUpdTrigger,
-								Oid ReferencingParentInsTrigger,
-								Oid ReferencingParentUpdTrigger)
+ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+								  Relation conrel, Relation tgrel,
+								  Oid fkrelid, Oid pkrelid,
+								  HeapTuple contuple, LOCKMODE lockmode,
+								  Oid ReferencedParentDelTrigger,
+								  Oid ReferencedParentUpdTrigger,
+								  Oid ReferencingParentInsTrigger,
+								  Oid ReferencingParentUpdTrigger)
 {
 	Form_pg_constraint currcon;
 	Oid			conoid;
@@ -12444,10 +12444,10 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 		 */
 		if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
 			get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
-			AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
-											 fkrelid, pkrelid, contuple,
-											 lockmode, InvalidOid, InvalidOid,
-											 InvalidOid, InvalidOid);
+			AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
+											   fkrelid, pkrelid, contuple,
+											   lockmode, InvalidOid, InvalidOid,
+											   InvalidOid, InvalidOid);
 
 		/* Drop all the triggers */
 		DropForeignKeyConstraintTriggers(tgrel, conoid, InvalidOid, InvalidOid);
@@ -12523,12 +12523,13 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 		 */
 		if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
 			get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
-			AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
-											 fkrelid, pkrelid, contuple,
-											 lockmode, ReferencedDelTriggerOid,
-											 ReferencedUpdTriggerOid,
-											 ReferencingInsTriggerOid,
-											 ReferencingUpdTriggerOid);
+			AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
+											   fkrelid, pkrelid, contuple,
+											   lockmode,
+											   ReferencedDelTriggerOid,
+											   ReferencedUpdTriggerOid,
+											   ReferencingInsTriggerOid,
+											   ReferencingUpdTriggerOid);
 	}
 
 	table_close(rel, NoLock);
@@ -12741,25 +12742,25 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
 }
 
 /*
- * Invokes ATExecAlterConstrEnforceability for each constraint that is a child of
- * the specified constraint.
+ * Invokes ATExecAlterFKConstrEnforceability for each foreign key constraint
+ * that is a child of the specified constraint.
  *
  * Note that this doesn't handle recursion the normal way, viz. by scanning the
  * list of child relations and recursing; instead it uses the conparentid
  * relationships.  This may need to be reconsidered.
  *
  * The arguments to this function have the same meaning as the arguments to
- * ATExecAlterConstrEnforceability.
+ * ATExecAlterFKConstrEnforceability.
  */
 static void
-AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
-								 Relation conrel, Relation tgrel,
-								 Oid fkrelid, Oid pkrelid,
-								 HeapTuple contuple, LOCKMODE lockmode,
-								 Oid ReferencedParentDelTrigger,
-								 Oid ReferencedParentUpdTrigger,
-								 Oid ReferencingParentInsTrigger,
-								 Oid ReferencingParentUpdTrigger)
+AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+								   Relation conrel, Relation tgrel,
+								   Oid fkrelid, Oid pkrelid,
+								   HeapTuple contuple, LOCKMODE lockmode,
+								   Oid ReferencedParentDelTrigger,
+								   Oid ReferencedParentUpdTrigger,
+								   Oid ReferencingParentInsTrigger,
+								   Oid ReferencingParentUpdTrigger)
 {
 	Form_pg_constraint currcon;
 	Oid			conoid;
@@ -12779,12 +12780,12 @@ AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 							   true, NULL, 1, &pkey);
 
 	while (HeapTupleIsValid(childtup = systable_getnext(pscan)))
-		ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
-										pkrelid, childtup, lockmode,
-										ReferencedParentDelTrigger,
-										ReferencedParentUpdTrigger,
-										ReferencingParentInsTrigger,
-										ReferencingParentUpdTrigger);
+		ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
+										  pkrelid, childtup, lockmode,
+										  ReferencedParentDelTrigger,
+										  ReferencedParentUpdTrigger,
+										  ReferencingParentInsTrigger,
+										  ReferencingParentUpdTrigger);
 
 	systable_endscan(pscan);
 }
-- 
2.34.1

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

syntax: ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED

commitfest: https://commitfest.postgresql.org/patch/5796
context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
discussion: https://postgr.es/m/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com
---
 doc/src/sgml/ref/alter_table.sgml         |   4 +-
 src/backend/commands/tablecmds.c          | 195 ++++++++++++++++++++--
 src/test/regress/expected/constraints.out |  79 +++++++++
 src/test/regress/expected/inherit.out     |  62 +++++++
 src/test/regress/sql/constraints.sql      |  51 ++++++
 src/test/regress/sql/inherit.sql          |  43 +++++
 6 files changed, 422 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 9d23ad5a0fb..b0cceba41c4 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -569,8 +569,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     <listitem>
      <para>
       This form alters the attributes of a constraint that was previously
-      created. Currently only foreign key constraints may be altered in
-      this fashion, but see below.
+      created. Currently <literal>FOREIGN KEY</literal> and <literal>CHECK</literal>
+      constraints may be altered in this fashion, but see below.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 752634018c2..7b207f12f0c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -404,6 +404,10 @@ static bool ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *
 											  Oid ReferencedParentUpdTrigger,
 											  Oid ReferencingParentInsTrigger,
 											  Oid ReferencingParentUpdTrigger);
+static bool ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+												 Relation conrel, HeapTuple contuple,
+												 bool recurse, bool recursing,
+												 LOCKMODE lockmode);
 static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon,
 										   Relation conrel, Relation tgrel, Relation rel,
 										   HeapTuple contuple, bool recurse,
@@ -422,6 +426,10 @@ static void AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint
 											   Oid ReferencedParentUpdTrigger,
 											   Oid ReferencingParentInsTrigger,
 											   Oid ReferencingParentUpdTrigger);
+static void AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+												  Relation conrel, Oid conrelid,
+												  bool recurse, bool recursing,
+												  LOCKMODE lockmode);
 static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 											Relation conrel, Relation tgrel, Relation rel,
 											HeapTuple contuple, bool recurse,
@@ -12181,7 +12189,7 @@ GetForeignKeyCheckTriggers(Relation trigrel,
  *
  * Update the attributes of a constraint.
  *
- * Currently only works for Foreign Key and not null constraints.
+ * Currently works for Foreign Key, Check, and not null constraints.
  *
  * If the constraint is modified, returns its address; otherwise, return
  * InvalidObjectAddress.
@@ -12243,11 +12251,13 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
 				 errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
 						cmdcon->conname, RelationGetRelationName(rel))));
-	if (cmdcon->alterEnforceability && currcon->contype != CONSTRAINT_FOREIGN)
+	if (cmdcon->alterEnforceability &&
+		(currcon->contype != CONSTRAINT_FOREIGN && currcon->contype != CONSTRAINT_CHECK))
 		ereport(ERROR,
-				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-				 errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"",
-						cmdcon->conname, RelationGetRelationName(rel))));
+				errcode(ERRCODE_WRONG_OBJECT_TYPE),
+				errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"",
+						cmdcon->conname, RelationGetRelationName(rel)),
+				errhint("Only foreign key and check constraints can change enforceability."));
 	if (cmdcon->alterInheritability &&
 		currcon->contype != CONSTRAINT_NOTNULL)
 		ereport(ERROR,
@@ -12353,12 +12363,21 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 	 * the trigger, during which the deferrability setting will be adjusted
 	 * automatically.
 	 */
-	if (cmdcon->alterEnforceability &&
-		ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
-										  currcon->conrelid, currcon->confrelid,
-										  contuple, lockmode, InvalidOid,
-										  InvalidOid, InvalidOid, InvalidOid))
+	if (cmdcon->alterEnforceability)
+	{
+		if (currcon->contype == CONSTRAINT_FOREIGN)
+			ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
+											  currcon->conrelid,
+											  currcon->confrelid,
+											  contuple, lockmode,
+											  InvalidOid, InvalidOid,
+											  InvalidOid, InvalidOid);
+		else if (currcon->contype == CONSTRAINT_CHECK)
+			ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel,
+												 contuple, recurse, false,
+												 lockmode);
 		changed = true;
+	}
 
 	else if (cmdcon->alterDeferrability &&
 			 ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12537,6 +12556,162 @@ ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 	return changed;
 }
 
+ /*
+  * Returns true if the CHECK constraint's enforceability is altered.
+  */
+static bool
+ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+									 Relation conrel, HeapTuple contuple,
+									 bool recurse, bool recursing, LOCKMODE lockmode)
+{
+	Form_pg_constraint currcon;
+	Relation	rel;
+	bool		changed = false;
+	List	   *children = NIL;
+
+	/* Since this function recurses, it could be driven to stack overflow */
+	check_stack_depth();
+
+	Assert(cmdcon->alterEnforceability);
+
+	currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+	Assert(currcon->contype == CONSTRAINT_CHECK);
+
+	/*
+	 * Parent relation already locked by called, children will be locked by
+	 * find_all_inheritors. So NoLock is fine here.
+	 */
+	rel = table_open(currcon->conrelid, NoLock);
+
+	if (currcon->conenforced != cmdcon->is_enforced)
+	{
+		AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple);
+		changed = true;
+	}
+
+	/*
+	 * Note that we must recurse even when trying to change a check constraint
+	 * to not enforced if it is already not enforced, in case descendant
+	 * constraints might be enforced and need to be changed to not enforced.
+	 * Conversely, we should do nothing if a constraint is being set to enforced
+	 * and is already enforced, as descendant constraints cannot be different in
+	 * that case.
+	 */
+	if (!cmdcon->is_enforced || changed)
+	{
+		/*
+		 * If we're recursing, the parent has already done this, so skip it.
+		 * Also, if the constraint is a NO INHERIT constraint, we shouldn't try
+		 * to look for it in the children.
+		 */
+		if (!recursing && !currcon->connoinherit)
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   lockmode, NULL);
+
+		foreach_oid(childoid, children)
+		{
+			if (childoid == RelationGetRelid(rel))
+				continue;
+
+			/*
+			 * If we are told not to recurse, there had better not be any child
+			 * tables, because we can't change constraint enforceability on the
+			 * parent unless we have changed enforceability for all child.
+			 */
+			if (!recurse)
+				ereport(ERROR,
+						errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						errmsg("constraint must be altered on child tables too"),
+						errhint("Do not specify the ONLY keyword."));
+
+			AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel,
+												  childoid, false, true,
+												  lockmode);
+		}
+	}
+
+	/*
+	 * Tell Phase 3 to check that the constraint is satisfied by existing rows.
+	 * This is needed only when altering the constraint from NOT ENFORCED to
+	 * ENFORCED.
+	 */
+	if (rel->rd_rel->relkind == RELKIND_RELATION &&
+		!currcon->conenforced &&
+		cmdcon->is_enforced)
+	{
+		AlteredTableInfo *tab;
+		NewConstraint *newcon;
+		Datum		val;
+		char	   *conbin;
+
+		newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+		newcon->name = pstrdup(NameStr(currcon->conname));
+		newcon->contype = CONSTR_CHECK;
+		val = SysCacheGetAttrNotNull(CONSTROID, contuple,
+									 Anum_pg_constraint_conbin);
+		conbin = TextDatumGetCString(val);
+		newcon->qual = expand_generated_columns_in_expr(stringToNode(conbin), rel, 1);
+
+		/* Find or create work queue entry for this table */
+		tab = ATGetQueueEntry(wqueue, rel);
+		tab->constraints = lappend(tab->constraints, newcon);
+	}
+
+	table_close(rel, NoLock);
+
+	return changed;
+}
+
+/*
+ * Invokes ATExecAlterCheckConstrEnforceability for each CHECK constraint that
+ * is a child of the specified constraint.
+ *
+ * We rely on the parent and child tables having identical CHECK constraint
+ * names to retrieve the child's pg_constraint tuple.
+ *
+ * The arguments to this function have the same meaning as the arguments to
+ * ATExecAlterCheckConstrEnforceability.
+ */
+static void
+AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+									  Relation conrel, Oid conrelid,
+									  bool recurse, bool recursing,
+									  LOCKMODE lockmode)
+{
+	SysScanDesc pscan;
+	HeapTuple	childtup;
+	ScanKeyData skey[3];
+
+	ScanKeyInit(&skey[0],
+				Anum_pg_constraint_conrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(conrelid));
+	ScanKeyInit(&skey[1],
+				Anum_pg_constraint_contypid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(InvalidOid));
+	ScanKeyInit(&skey[2],
+				Anum_pg_constraint_conname,
+				BTEqualStrategyNumber, F_NAMEEQ,
+				CStringGetDatum(cmdcon->conname));
+
+	pscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
+							   NULL, 3, skey);
+
+	if (!HeapTupleIsValid(childtup = systable_getnext(pscan)))
+		ereport(ERROR,
+				errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+					   cmdcon->conname, get_rel_name(conrelid)));
+
+	ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel, childtup,
+										 recurse, recursing, lockmode);
+
+	systable_endscan(pscan);
+}
+
+
 /*
  * Returns true if the constraint's deferrability is altered.
  *
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 1bbf59cca02..c0cf19d0eb0 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -390,6 +390,83 @@ SELECT * FROM COPY_TBL;
  6 | OK            | 4
 (2 rows)
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+create table parted_ch(
+  a int, b int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) not enforced
+) partition by range(a);
+create table parted_ch_1 partition of parted_ch for values from (0) to (10) partition by list(b);
+create table parted_ch_11 partition of parted_ch_1 for values in (0, 1, 22,4);
+create table parted_ch_12 partition of parted_ch_1 for values in (2);
+create table parted_ch_2(b int, a int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) enforced,
+  constraint cc_2 check( a < 15) not enforced
+);
+alter table parted_ch attach partition parted_ch_2 for values from (10) to (20);
+insert into parted_ch values (1, 22), (9, 1), (16, 16);
+alter table parted_ch alter constraint cc_1 enforced; --error
+ERROR:  check constraint "cc_1" of relation "parted_ch_11" is violated by some row
+update parted_ch set b = 4 where b = 22;
+alter table parted_ch alter constraint cc_1 enforced; --ok
+create or replace view check_constraint_status as
+select  conname, conrelid::regclass, conenforced, convalidated
+from    pg_constraint
+where   conrelid::regclass::text ~* '^parted_ch' and contype = 'c'
+order by conname, conrelid::regclass::text collate "C";
+alter table parted_ch alter constraint cc not enforced; --no-op
+alter table parted_ch alter constraint cc enforced; --error
+ERROR:  check constraint "cc" of relation "parted_ch_11" is violated by some row
+delete from parted_ch where a = 1;
+alter table parted_ch alter constraint cc enforced; --error
+ERROR:  check constraint "cc" of relation "parted_ch_11" is violated by some row
+delete from parted_ch where a = 9;
+alter table parted_ch alter constraint cc enforced;
+--check these CHECK constraint status
+select * from check_constraint_status;
+ conname |   conrelid   | conenforced | convalidated 
+---------+--------------+-------------+--------------
+ cc      | parted_ch    | t           | t
+ cc      | parted_ch_1  | t           | t
+ cc      | parted_ch_11 | t           | t
+ cc      | parted_ch_12 | t           | t
+ cc      | parted_ch_2  | t           | t
+ cc_1    | parted_ch    | t           | t
+ cc_1    | parted_ch_1  | t           | t
+ cc_1    | parted_ch_11 | t           | t
+ cc_1    | parted_ch_12 | t           | t
+ cc_1    | parted_ch_2  | t           | t
+ cc_2    | parted_ch_2  | f           | f
+(11 rows)
+
+alter table parted_ch_2 alter constraint cc_2 enforced; --error
+ERROR:  check constraint "cc_2" of relation "parted_ch_2" is violated by some row
+delete from parted_ch where a = 16;
+alter table parted_ch_2 alter constraint cc_2 enforced;
+alter table parted_ch_2 alter constraint cc not enforced;
+alter table parted_ch_2 alter constraint cc_1 not enforced;
+alter table parted_ch_2 alter constraint cc_2 not enforced;
+--check these CHECK constraint status again
+select * from check_constraint_status;
+ conname |   conrelid   | conenforced | convalidated 
+---------+--------------+-------------+--------------
+ cc      | parted_ch    | t           | t
+ cc      | parted_ch_1  | t           | t
+ cc      | parted_ch_11 | t           | t
+ cc      | parted_ch_12 | t           | t
+ cc      | parted_ch_2  | f           | f
+ cc_1    | parted_ch    | t           | t
+ cc_1    | parted_ch_1  | t           | t
+ cc_1    | parted_ch_11 | t           | t
+ cc_1    | parted_ch_12 | t           | t
+ cc_1    | parted_ch_2  | f           | f
+ cc_2    | parted_ch_2  | f           | f
+(11 rows)
+
+drop table parted_ch;
 --
 -- Primary keys
 --
@@ -746,8 +823,10 @@ LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
                                                    ^
 ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
 ERROR:  cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
+HINT:  Only foreign key and check constraints can change enforceability.
 ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED;
 ERROR:  cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
+HINT:  Only foreign key and check constraints can change enforceability.
 -- can't make an existing constraint NOT VALID
 ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT VALID;
 ERROR:  constraints cannot be altered to be NOT VALID
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 0490a746555..36f81f39265 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1421,11 +1421,54 @@ order by 1, 2;
  p1_c3   | inh_check_constraint9  | f          |           2 | t           | t
 (38 rows)
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  constraint must be altered on child tables too
+HINT:  Do not specify the ONLY keyword.
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+ERROR:  constraint must be altered on child tables too
+HINT:  Do not specify the ONLY keyword.
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+alter table p1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  check constraint "inh_check_constraint3" of relation "p1_c1" is violated by some row
+delete from only p1_c1 where f1 = -2;
+alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  check constraint "inh_check_constraint3" of relation "p1_c3" is violated by some row
+delete from only p1_c3 where f1 = -3;
+alter table p1 alter constraint inh_check_constraint3 enforced; --ok
+alter table p1 alter constraint inh_check_constraint3 not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'inh_check_constraint3' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+        conname        | conenforced | convalidated | conrelid 
+-----------------------+-------------+--------------+----------
+ inh_check_constraint3 | f           | f            | p1
+ inh_check_constraint3 | f           | f            | p1_c1
+ inh_check_constraint3 | f           | f            | p1_c2
+ inh_check_constraint3 | f           | f            | p1_c3
+(4 rows)
+
 drop table p1 cascade;
 NOTICE:  drop cascades to 3 other objects
 DETAIL:  drop cascades to table p1_c1
 drop cascades to table p1_c2
 drop cascades to table p1_c3
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1_c1" is violated by some row
+drop table p1 cascade;
+NOTICE:  drop cascades to table p1_c1
 --
 -- Similarly, check the merging of existing constraints; a parent constraint
 -- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
@@ -1434,6 +1477,25 @@ drop cascades to table p1_c3
 create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
 alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+ERROR:  new row for relation "p1_c1" violates check constraint "p1_a_check"
+DETAIL:  Failing row contains (-1).
+alter table p1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1" is violated by some row
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1 alter constraint p1_a_check not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+  conname   | conenforced | convalidated | conrelid 
+------------+-------------+--------------+----------
+ p1_a_check | f           | f            | p1
+ p1_a_check | f           | f            | p1_c1
+(2 rows)
+
 drop table p1 cascade;
 NOTICE:  drop cascades to table p1_c1
 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 733a1dbccfe..514ef4b2a50 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -266,6 +266,57 @@ COPY COPY_TBL FROM :'filename';
 
 SELECT * FROM COPY_TBL;
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+create table parted_ch(
+  a int, b int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) not enforced
+) partition by range(a);
+create table parted_ch_1 partition of parted_ch for values from (0) to (10) partition by list(b);
+create table parted_ch_11 partition of parted_ch_1 for values in (0, 1, 22,4);
+create table parted_ch_12 partition of parted_ch_1 for values in (2);
+create table parted_ch_2(b int, a int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) enforced,
+  constraint cc_2 check( a < 15) not enforced
+);
+alter table parted_ch attach partition parted_ch_2 for values from (10) to (20);
+
+insert into parted_ch values (1, 22), (9, 1), (16, 16);
+
+alter table parted_ch alter constraint cc_1 enforced; --error
+update parted_ch set b = 4 where b = 22;
+alter table parted_ch alter constraint cc_1 enforced; --ok
+
+create or replace view check_constraint_status as
+select  conname, conrelid::regclass, conenforced, convalidated
+from    pg_constraint
+where   conrelid::regclass::text ~* '^parted_ch' and contype = 'c'
+order by conname, conrelid::regclass::text collate "C";
+
+alter table parted_ch alter constraint cc not enforced; --no-op
+alter table parted_ch alter constraint cc enforced; --error
+delete from parted_ch where a = 1;
+alter table parted_ch alter constraint cc enforced; --error
+delete from parted_ch where a = 9;
+alter table parted_ch alter constraint cc enforced;
+
+--check these CHECK constraint status
+select * from check_constraint_status;
+
+alter table parted_ch_2 alter constraint cc_2 enforced; --error
+delete from parted_ch where a = 16;
+alter table parted_ch_2 alter constraint cc_2 enforced;
+alter table parted_ch_2 alter constraint cc not enforced;
+alter table parted_ch_2 alter constraint cc_1 not enforced;
+alter table parted_ch_2 alter constraint cc_2 not enforced;
+
+--check these CHECK constraint status again
+select * from check_constraint_status;
+drop table parted_ch;
+
 --
 -- Primary keys
 --
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..8f986904389 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -510,6 +510,38 @@ select conrelid::regclass::text as relname, conname, conislocal, coninhcount, co
 from pg_constraint where conname like 'inh\_check\_constraint%'
 order by 1, 2;
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+
+alter table p1 alter constraint inh_check_constraint3 enforced; --error
+delete from only p1_c1 where f1 = -2;
+alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error
+
+delete from only p1_c3 where f1 = -3;
+alter table p1 alter constraint inh_check_constraint3 enforced; --ok
+alter table p1 alter constraint inh_check_constraint3 not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'inh_check_constraint3' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+drop table p1 cascade;
+
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
 drop table p1 cascade;
 
 --
@@ -520,6 +552,17 @@ drop table p1 cascade;
 create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
 alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+alter table p1 alter constraint p1_a_check enforced; --error
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1 alter constraint p1_a_check not enforced; --ok
+
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
 drop table p1 cascade;
 
 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
-- 
2.34.1

From 93d679d9f2b9fe7bdbec511d19211158312ff4f3 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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 <jian.universality@gmail.com>
Reviewed-by: Amul Sul <sulamul@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>,
Reviewed-by: Robert Treat <rob@xzilla.net>

(XXX delete this line later) commitfest: https://commitfest.postgresql.org/patch/5796
discussion: https://postgr.es/m/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com
---
 src/backend/commands/tablecmds.c | 119 ++++++++++++++++---------------
 1 file changed, 60 insertions(+), 59 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6b1a00ed477..dc906a16489 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -396,14 +396,14 @@ static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel,
 static bool ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel,
 										  Relation tgrel, Relation rel, HeapTuple contuple,
 										  bool recurse, LOCKMODE lockmode);
-static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
-											Relation conrel, Relation tgrel,
-											Oid fkrelid, Oid pkrelid,
-											HeapTuple contuple, LOCKMODE lockmode,
-											Oid ReferencedParentDelTrigger,
-											Oid ReferencedParentUpdTrigger,
-											Oid ReferencingParentInsTrigger,
-											Oid ReferencingParentUpdTrigger);
+static bool ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+											  Relation conrel, Relation tgrel,
+											  Oid fkrelid, Oid pkrelid,
+											  HeapTuple contuple, LOCKMODE lockmode,
+											  Oid ReferencedParentDelTrigger,
+											  Oid ReferencedParentUpdTrigger,
+											  Oid ReferencingParentInsTrigger,
+											  Oid ReferencingParentUpdTrigger);
 static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon,
 										   Relation conrel, Relation tgrel, Relation rel,
 										   HeapTuple contuple, bool recurse,
@@ -414,14 +414,14 @@ static bool ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cm
 static void AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
 											bool deferrable, bool initdeferred,
 											List **otherrelids);
-static void AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
-											 Relation conrel, Relation tgrel,
-											 Oid fkrelid, Oid pkrelid,
-											 HeapTuple contuple, LOCKMODE lockmode,
-											 Oid ReferencedParentDelTrigger,
-											 Oid ReferencedParentUpdTrigger,
-											 Oid ReferencingParentInsTrigger,
-											 Oid ReferencingParentUpdTrigger);
+static void AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+											   Relation conrel, Relation tgrel,
+											   Oid fkrelid, Oid pkrelid,
+											   HeapTuple contuple, LOCKMODE lockmode,
+											   Oid ReferencedParentDelTrigger,
+											   Oid ReferencedParentUpdTrigger,
+											   Oid ReferencingParentInsTrigger,
+											   Oid ReferencingParentUpdTrigger);
 static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 											Relation conrel, Relation tgrel, Relation rel,
 											HeapTuple contuple, bool recurse,
@@ -12385,15 +12385,15 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 	 * enforceability, we don't need to explicitly update multiple entries in
 	 * pg_trigger related to deferrability.
 	 *
-	 * Modifying enforceability involves either creating or dropping the
-	 * trigger, during which the deferrability setting will be adjusted
-	 * automatically.
+	 * Modifying foreign key enforceability involves either creating or
+	 * dropping the trigger, during which the deferrability setting will be
+	 * adjusted automatically.
 	 */
 	if (cmdcon->alterEnforceability &&
-		ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
-										currcon->conrelid, currcon->confrelid,
-										contuple, lockmode, InvalidOid,
-										InvalidOid, InvalidOid, InvalidOid))
+		ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
+										  currcon->conrelid, currcon->confrelid,
+										  contuple, lockmode, InvalidOid,
+										  InvalidOid, InvalidOid, InvalidOid))
 		changed = true;
 
 	else if (cmdcon->alterDeferrability &&
@@ -12425,7 +12425,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.
@@ -12437,14 +12437,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;
@@ -12480,10 +12480,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);
@@ -12559,12 +12559,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);
@@ -12777,25 +12778,25 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
 }
 
 /*
- * Invokes ATExecAlterConstrEnforceability for each constraint that is a child of
- * the specified constraint.
+ * Invokes ATExecAlterFKConstrEnforceability for each foreign key constraint
+ * that is a child of the specified constraint.
  *
  * Note that this doesn't handle recursion the normal way, viz. by scanning the
  * list of child relations and recursing; instead it uses the conparentid
  * relationships.  This may need to be reconsidered.
  *
  * The arguments to this function have the same meaning as the arguments to
- * ATExecAlterConstrEnforceability.
+ * ATExecAlterFKConstrEnforceability.
  */
 static void
-AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
-								 Relation conrel, Relation tgrel,
-								 Oid fkrelid, Oid pkrelid,
-								 HeapTuple contuple, LOCKMODE lockmode,
-								 Oid ReferencedParentDelTrigger,
-								 Oid ReferencedParentUpdTrigger,
-								 Oid ReferencingParentInsTrigger,
-								 Oid ReferencingParentUpdTrigger)
+AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+								   Relation conrel, Relation tgrel,
+								   Oid fkrelid, Oid pkrelid,
+								   HeapTuple contuple, LOCKMODE lockmode,
+								   Oid ReferencedParentDelTrigger,
+								   Oid ReferencedParentUpdTrigger,
+								   Oid ReferencingParentInsTrigger,
+								   Oid ReferencingParentUpdTrigger)
 {
 	Form_pg_constraint currcon;
 	Oid			conoid;
@@ -12815,12 +12816,12 @@ AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 							   true, NULL, 1, &pkey);
 
 	while (HeapTupleIsValid(childtup = systable_getnext(pscan)))
-		ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
-										pkrelid, childtup, lockmode,
-										ReferencedParentDelTrigger,
-										ReferencedParentUpdTrigger,
-										ReferencingParentInsTrigger,
-										ReferencingParentUpdTrigger);
+		ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
+										  pkrelid, childtup, lockmode,
+										  ReferencedParentDelTrigger,
+										  ReferencedParentUpdTrigger,
+										  ReferencingParentInsTrigger,
+										  ReferencingParentUpdTrigger);
 
 	systable_endscan(pscan);
 }
-- 
2.34.1

From ba246abdb3df94e3e4f44d4301596890f59d413f Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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 <jian.universality@gmail.com>
Reviewed-by: Amul Sul <sulamul@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>,
Reviewed-by: Robert Treat <rob@xzilla.net>

(XXX delete this line later) commitfest: https://commitfest.postgresql.org/patch/5796
discussion: https://postgr.es/m/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com
---
 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(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 9abd8037f28..58fcbfb33c0 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -575,8 +575,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     <listitem>
      <para>
       This form alters the attributes of a constraint that was previously
-      created. Currently only foreign key constraints may be altered in
-      this fashion, but see below.
+      created. Currently <literal>FOREIGN KEY</literal> and <literal>CHECK</literal>
+      constraints may be altered in this fashion, but see below.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dc906a16489..5fb5fa6c111 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,
@@ -12217,7 +12225,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.
@@ -12279,11 +12287,13 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
 				 errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
 						cmdcon->conname, RelationGetRelationName(rel))));
-	if (cmdcon->alterEnforceability && currcon->contype != CONSTRAINT_FOREIGN)
+	if (cmdcon->alterEnforceability &&
+		(currcon->contype != CONSTRAINT_FOREIGN && currcon->contype != CONSTRAINT_CHECK))
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
 				 errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"",
-						cmdcon->conname, RelationGetRelationName(rel))));
+						cmdcon->conname, RelationGetRelationName(rel)),
+				 errhint("Only foreign key and check constraints can change enforceability.")));
 	if (cmdcon->alterInheritability &&
 		currcon->contype != CONSTRAINT_NOTNULL)
 		ereport(ERROR,
@@ -12389,12 +12399,21 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 	 * dropping the trigger, during which the deferrability setting will be
 	 * adjusted automatically.
 	 */
-	if (cmdcon->alterEnforceability &&
-		ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
-										  currcon->conrelid, currcon->confrelid,
-										  contuple, lockmode, InvalidOid,
-										  InvalidOid, InvalidOid, InvalidOid))
+	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,
@@ -12573,6 +12592,163 @@ ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 	return changed;
 }
 
+ /*
+  * Returns true if the CHECK constraint's enforceability is altered.
+  */
+static bool
+ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+									 Relation conrel, HeapTuple contuple,
+									 bool recurse, bool recursing, LOCKMODE lockmode)
+{
+	Form_pg_constraint currcon;
+	Relation	rel;
+	bool		changed = false;
+	List	   *children = NIL;
+
+	/* Since this function recurses, it could be driven to stack overflow */
+	check_stack_depth();
+
+	Assert(cmdcon->alterEnforceability);
+
+	currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+	Assert(currcon->contype == CONSTRAINT_CHECK);
+
+	/*
+	 * Parent relation already locked by caller, children will be locked by
+	 * find_all_inheritors. So NoLock is fine here.
+	 */
+	rel = table_open(currcon->conrelid, NoLock);
+
+	if (currcon->conenforced != cmdcon->is_enforced)
+	{
+		AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple);
+		changed = true;
+	}
+
+	/*
+	 * Note that we must recurse even when trying to change a check constraint
+	 * to not enforced if it is already not enforced, in case descendant
+	 * constraints might be enforced and need to be changed to not enforced.
+	 * Conversely, we should do nothing if a constraint is being set to
+	 * enforced and is already enforced, as descendant constraints cannot be
+	 * different in that case.
+	 */
+	if (!cmdcon->is_enforced || changed)
+	{
+		/*
+		 * If we're recursing, the parent has already done this, so skip it.
+		 * Also, if the constraint is a NO INHERIT constraint, we shouldn't
+		 * try to look for it in the children.
+		 */
+		if (!recursing && !currcon->connoinherit)
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   lockmode, NULL);
+
+		foreach_oid(childoid, children)
+		{
+			if (childoid == RelationGetRelid(rel))
+				continue;
+
+			/*
+			 * If we are told not to recurse, there had better not be any
+			 * child tables, because we can't change constraint enforceability
+			 * on the parent unless we have changed enforceability for all
+			 * child.
+			 */
+			if (!recurse)
+				ereport(ERROR,
+						errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						errmsg("constraint must be altered on child tables too"),
+						errhint("Do not specify the ONLY keyword."));
+
+			AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel,
+												  childoid, false, true,
+												  lockmode);
+		}
+	}
+
+	/*
+	 * Tell Phase 3 to check that the constraint is satisfied by existing
+	 * rows. We only need do this when altering the constraint from NOT
+	 * ENFORCED to ENFORCED.
+	 */
+	if (rel->rd_rel->relkind == RELKIND_RELATION &&
+		!currcon->conenforced &&
+		cmdcon->is_enforced)
+	{
+		AlteredTableInfo *tab;
+		NewConstraint *newcon;
+		Datum		val;
+		char	   *conbin;
+
+		newcon = palloc0_object(NewConstraint);
+		newcon->name = pstrdup(NameStr(currcon->conname));
+		newcon->contype = CONSTR_CHECK;
+
+		val = SysCacheGetAttrNotNull(CONSTROID, contuple,
+									 Anum_pg_constraint_conbin);
+		conbin = TextDatumGetCString(val);
+		newcon->qual = expand_generated_columns_in_expr(stringToNode(conbin), rel, 1);
+
+		/* Find or create work queue entry for this table */
+		tab = ATGetQueueEntry(wqueue, rel);
+		tab->constraints = lappend(tab->constraints, newcon);
+	}
+
+	table_close(rel, NoLock);
+
+	return changed;
+}
+
+/*
+ * Invokes ATExecAlterCheckConstrEnforceability for each CHECK constraint that
+ * is a child of the specified constraint.
+ *
+ * We rely on the parent and child tables having identical CHECK constraint
+ * names to retrieve the child's pg_constraint tuple.
+ *
+ * The arguments to this function have the same meaning as the arguments to
+ * ATExecAlterCheckConstrEnforceability.
+ */
+static void
+AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+									  Relation conrel, Oid conrelid,
+									  bool recurse, bool recursing,
+									  LOCKMODE lockmode)
+{
+	SysScanDesc pscan;
+	HeapTuple	childtup;
+	ScanKeyData skey[3];
+
+	ScanKeyInit(&skey[0],
+				Anum_pg_constraint_conrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(conrelid));
+	ScanKeyInit(&skey[1],
+				Anum_pg_constraint_contypid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(InvalidOid));
+	ScanKeyInit(&skey[2],
+				Anum_pg_constraint_conname,
+				BTEqualStrategyNumber, F_NAMEEQ,
+				CStringGetDatum(cmdcon->conname));
+
+	pscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
+							   NULL, 3, skey);
+
+	if (!HeapTupleIsValid(childtup = systable_getnext(pscan)))
+		ereport(ERROR,
+				errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+					   cmdcon->conname, get_rel_name(conrelid)));
+
+	ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel, childtup,
+										 recurse, recursing, lockmode);
+
+	systable_endscan(pscan);
+}
+
 /*
  * Returns true if the constraint's deferrability is altered.
  *
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 1bbf59cca02..3850e9925c0 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -390,6 +390,84 @@ SELECT * FROM COPY_TBL;
  6 | OK            | 4
 (2 rows)
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+create table parted_ch(
+  a int, b int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) not enforced
+) partition by range(a);
+create table parted_ch_1 partition of parted_ch for values from (0) to (10) partition by list(b);
+create table parted_ch_11 partition of parted_ch_1 for values in (0, 1, 22,4);
+create table parted_ch_12 partition of parted_ch_1 for values in (2);
+create table parted_ch_2(b int, a int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) enforced,
+  constraint cc_2 check( a < 15) not enforced
+);
+alter table parted_ch attach partition parted_ch_2 for values from (10) to (20);
+insert into parted_ch values (1, 22), (9, 1), (16, 16);
+alter table parted_ch alter constraint cc_1 enforced; --error
+ERROR:  check constraint "cc_1" of relation "parted_ch_11" is violated by some row
+update parted_ch set b = 4 where b = 22;
+alter table parted_ch alter constraint cc_1 enforced; --ok
+create or replace view check_constraint_status as
+select  conname, conrelid::regclass, conenforced, convalidated
+from    pg_constraint
+where   conrelid::regclass::text ~* '^parted_ch' and contype = 'c'
+order by conname, conrelid::regclass::text collate "C";
+alter table parted_ch alter constraint cc not enforced; --no-op
+alter table parted_ch alter constraint cc enforced; --error
+ERROR:  check constraint "cc" of relation "parted_ch_11" is violated by some row
+delete from parted_ch where a = 1;
+alter table parted_ch alter constraint cc enforced; --error
+ERROR:  check constraint "cc" of relation "parted_ch_11" is violated by some row
+delete from parted_ch where a = 9;
+alter table parted_ch alter constraint cc enforced;
+--check these CHECK constraint status
+select * from check_constraint_status;
+ conname |   conrelid   | conenforced | convalidated 
+---------+--------------+-------------+--------------
+ cc      | parted_ch    | t           | t
+ cc      | parted_ch_1  | t           | t
+ cc      | parted_ch_11 | t           | t
+ cc      | parted_ch_12 | t           | t
+ cc      | parted_ch_2  | t           | t
+ cc_1    | parted_ch    | t           | t
+ cc_1    | parted_ch_1  | t           | t
+ cc_1    | parted_ch_11 | t           | t
+ cc_1    | parted_ch_12 | t           | t
+ cc_1    | parted_ch_2  | t           | t
+ cc_2    | parted_ch_2  | f           | f
+(11 rows)
+
+alter table parted_ch_2 alter constraint cc_2 enforced; --error
+ERROR:  check constraint "cc_2" of relation "parted_ch_2" is violated by some row
+delete from parted_ch where a = 16;
+alter table parted_ch_2 alter constraint cc_2 enforced;
+alter table parted_ch_2 alter constraint cc not enforced;
+alter table parted_ch_2 alter constraint cc_1 not enforced;
+alter table parted_ch_2 alter constraint cc_2 not enforced;
+--check these CHECK constraint status again
+select * from check_constraint_status;
+ conname |   conrelid   | conenforced | convalidated 
+---------+--------------+-------------+--------------
+ cc      | parted_ch    | t           | t
+ cc      | parted_ch_1  | t           | t
+ cc      | parted_ch_11 | t           | t
+ cc      | parted_ch_12 | t           | t
+ cc      | parted_ch_2  | f           | f
+ cc_1    | parted_ch    | t           | t
+ cc_1    | parted_ch_1  | t           | t
+ cc_1    | parted_ch_11 | t           | t
+ cc_1    | parted_ch_12 | t           | t
+ cc_1    | parted_ch_2  | f           | f
+ cc_2    | parted_ch_2  | f           | f
+(11 rows)
+
+drop table parted_ch;
+drop view check_constraint_status;
 --
 -- Primary keys
 --
@@ -746,8 +824,10 @@ LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
                                                    ^
 ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
 ERROR:  cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
+HINT:  Only foreign key and check constraints can change enforceability.
 ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED;
 ERROR:  cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
+HINT:  Only foreign key and check constraints can change enforceability.
 -- can't make an existing constraint NOT VALID
 ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT VALID;
 ERROR:  constraints cannot be altered to be NOT VALID
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 0490a746555..36f81f39265 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1421,11 +1421,54 @@ order by 1, 2;
  p1_c3   | inh_check_constraint9  | f          |           2 | t           | t
 (38 rows)
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  constraint must be altered on child tables too
+HINT:  Do not specify the ONLY keyword.
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+ERROR:  constraint must be altered on child tables too
+HINT:  Do not specify the ONLY keyword.
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+alter table p1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  check constraint "inh_check_constraint3" of relation "p1_c1" is violated by some row
+delete from only p1_c1 where f1 = -2;
+alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error
+ERROR:  check constraint "inh_check_constraint3" of relation "p1_c3" is violated by some row
+delete from only p1_c3 where f1 = -3;
+alter table p1 alter constraint inh_check_constraint3 enforced; --ok
+alter table p1 alter constraint inh_check_constraint3 not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'inh_check_constraint3' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+        conname        | conenforced | convalidated | conrelid 
+-----------------------+-------------+--------------+----------
+ inh_check_constraint3 | f           | f            | p1
+ inh_check_constraint3 | f           | f            | p1_c1
+ inh_check_constraint3 | f           | f            | p1_c2
+ inh_check_constraint3 | f           | f            | p1_c3
+(4 rows)
+
 drop table p1 cascade;
 NOTICE:  drop cascades to 3 other objects
 DETAIL:  drop cascades to table p1_c1
 drop cascades to table p1_c2
 drop cascades to table p1_c3
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1_c1" is violated by some row
+drop table p1 cascade;
+NOTICE:  drop cascades to table p1_c1
 --
 -- Similarly, check the merging of existing constraints; a parent constraint
 -- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
@@ -1434,6 +1477,25 @@ drop cascades to table p1_c3
 create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
 alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+ERROR:  new row for relation "p1_c1" violates check constraint "p1_a_check"
+DETAIL:  Failing row contains (-1).
+alter table p1 alter constraint p1_a_check enforced; --error
+ERROR:  check constraint "p1_a_check" of relation "p1" is violated by some row
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1 alter constraint p1_a_check not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+  conname   | conenforced | convalidated | conrelid 
+------------+-------------+--------------+----------
+ p1_a_check | f           | f            | p1
+ p1_a_check | f           | f            | p1_c1
+(2 rows)
+
 drop table p1 cascade;
 NOTICE:  drop cascades to table p1_c1
 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 733a1dbccfe..9609a67dda0 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -266,6 +266,58 @@ COPY COPY_TBL FROM :'filename';
 
 SELECT * FROM COPY_TBL;
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+create table parted_ch(
+  a int, b int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) not enforced
+) partition by range(a);
+create table parted_ch_1 partition of parted_ch for values from (0) to (10) partition by list(b);
+create table parted_ch_11 partition of parted_ch_1 for values in (0, 1, 22,4);
+create table parted_ch_12 partition of parted_ch_1 for values in (2);
+create table parted_ch_2(b int, a int,
+  constraint cc check (a > 10) not enforced,
+  constraint cc_1 check (b < 17) enforced,
+  constraint cc_2 check( a < 15) not enforced
+);
+alter table parted_ch attach partition parted_ch_2 for values from (10) to (20);
+
+insert into parted_ch values (1, 22), (9, 1), (16, 16);
+
+alter table parted_ch alter constraint cc_1 enforced; --error
+update parted_ch set b = 4 where b = 22;
+alter table parted_ch alter constraint cc_1 enforced; --ok
+
+create or replace view check_constraint_status as
+select  conname, conrelid::regclass, conenforced, convalidated
+from    pg_constraint
+where   conrelid::regclass::text ~* '^parted_ch' and contype = 'c'
+order by conname, conrelid::regclass::text collate "C";
+
+alter table parted_ch alter constraint cc not enforced; --no-op
+alter table parted_ch alter constraint cc enforced; --error
+delete from parted_ch where a = 1;
+alter table parted_ch alter constraint cc enforced; --error
+delete from parted_ch where a = 9;
+alter table parted_ch alter constraint cc enforced;
+
+--check these CHECK constraint status
+select * from check_constraint_status;
+
+alter table parted_ch_2 alter constraint cc_2 enforced; --error
+delete from parted_ch where a = 16;
+alter table parted_ch_2 alter constraint cc_2 enforced;
+alter table parted_ch_2 alter constraint cc not enforced;
+alter table parted_ch_2 alter constraint cc_1 not enforced;
+alter table parted_ch_2 alter constraint cc_2 not enforced;
+
+--check these CHECK constraint status again
+select * from check_constraint_status;
+drop table parted_ch;
+drop view check_constraint_status;
+
 --
 -- Primary keys
 --
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..8f986904389 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -510,6 +510,38 @@ select conrelid::regclass::text as relname, conname, conislocal, coninhcount, co
 from pg_constraint where conname like 'inh\_check\_constraint%'
 order by 1, 2;
 
+--
+-- CHECK constraints
+-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED
+alter table p1 drop constraint inh_check_constraint1;
+alter table p1_c1 drop constraint inh_check_constraint1;
+
+alter table only p1 alter constraint inh_check_constraint3 enforced; --error
+alter table only p1 alter constraint inh_check_constraint3 not enforced; --error
+
+insert into p1_c1 values(-2);
+insert into p1_c3 values(-3);
+
+alter table p1 alter constraint inh_check_constraint3 enforced; --error
+delete from only p1_c1 where f1 = -2;
+alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error
+
+delete from only p1_c3 where f1 = -3;
+alter table p1 alter constraint inh_check_constraint3 enforced; --ok
+alter table p1 alter constraint inh_check_constraint3 not enforced; --ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'inh_check_constraint3' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+drop table p1 cascade;
+
+--for "no inherit" check constraint, it will not recurse to child table
+create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
+create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
+alter table p1_c1 inherit p1;
+insert into p1_c1 values(-11);
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1_c1 alter constraint p1_a_check enforced; --error
 drop table p1 cascade;
 
 --
@@ -520,6 +552,17 @@ drop table p1 cascade;
 create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced);
 alter table p1_c1 inherit p1;
+insert into p1 values(-1); --ok
+insert into p1_c1 values(-1); --error
+alter table p1 alter constraint p1_a_check enforced; --error
+truncate p1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+alter table p1 alter constraint p1_a_check not enforced; --ok
+
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
 drop table p1 cascade;
 
 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
-- 
2.34.1