v1-0001-UPDATE-run-check-constraints-for-affected-columns-only.patch

From 51dca341ee334a04e5dc296fe1da8af6851c6781 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 1 Dec 2025 12:45:08 +0800
Subject: [PATCH v1 1/1] UPDATE run check constraints for affected columns only

discussion: https://postgr.es/m/
context: https://postgr.es/m/1326055327.15293.13.camel%40vanquo.pezone.net
---
 src/backend/commands/copyfrom.c               |  2 +-
 src/backend/executor/execMain.c               | 45 +++++++++++++++--
 src/backend/executor/execReplication.c        |  4 +-
 src/backend/executor/nodeModifyTable.c        |  4 +-
 src/include/executor/executor.h               |  2 +-
 src/include/nodes/execnodes.h                 |  7 +++
 .../test_misc/t/001_constraint_validation.pl  | 50 +++++++++++++++++++
 7 files changed, 105 insertions(+), 9 deletions(-)

diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 12781963b4f..52e2bb983e6 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1355,7 +1355,7 @@ CopyFrom(CopyFromState cstate)
 				 */
 				if (resultRelInfo->ri_FdwRoutine == NULL &&
 					resultRelInfo->ri_RelationDesc->rd_att->constr)
-					ExecConstraints(resultRelInfo, myslot, estate);
+					ExecConstraints(CMD_INSERT, resultRelInfo, myslot, estate);
 
 				/*
 				 * Also check the tuple against the partition constraint, if
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 27c9eec697b..b157eacd1dc 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -52,6 +52,7 @@
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
 #include "nodes/queryjumble.h"
+#include "optimizer/optimizer.h"
 #include "parser/parse_relation.h"
 #include "pgstat.h"
 #include "rewrite/rewriteHandler.h"
@@ -1775,7 +1776,7 @@ ExecutePlan(QueryDesc *queryDesc,
  * Returns NULL if OK, else name of failed check constraint
  */
 static const char *
-ExecRelCheck(ResultRelInfo *resultRelInfo,
+ExecRelCheck(CmdType operation, ResultRelInfo *resultRelInfo,
 			 TupleTableSlot *slot, EState *estate)
 {
 	Relation	rel = resultRelInfo->ri_RelationDesc;
@@ -1800,8 +1801,16 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 	 */
 	if (resultRelInfo->ri_CheckConstraintExprs == NULL)
 	{
+		Bitmapset  *updatedCols;
+		Bitmapset  *extraUpdatedCols;
+
 		oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
 		resultRelInfo->ri_CheckConstraintExprs = palloc0_array(ExprState *, ncheck);
+
+		resultRelInfo->ri_UpdateSkipConstrCheck = palloc0(sizeof(bool) * ncheck);
+		updatedCols = ExecGetUpdatedCols(resultRelInfo, estate);
+		extraUpdatedCols = ExecGetExtraUpdatedCols(resultRelInfo, estate);
+
 		for (int i = 0; i < ncheck; i++)
 		{
 			Expr	   *checkconstr;
@@ -1814,6 +1823,32 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 			checkconstr = (Expr *) expand_generated_columns_in_expr((Node *) checkconstr, rel, 1);
 			resultRelInfo->ri_CheckConstraintExprs[i] =
 				ExecPrepareExpr(checkconstr, estate);
+
+			if (operation == CMD_UPDATE)
+			{
+				Bitmapset  *check_attrs = NULL;
+
+				pull_varattnos((Node *) checkconstr, 1, &check_attrs);
+
+				/*
+				 * If this constraint has no Var reference or contains a
+				 * whole-row Var reference, then we cannot skip the
+				 * verification.
+				 */
+				if (!check_attrs || bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, check_attrs))
+					continue;
+
+				if (!bms_overlap(check_attrs, updatedCols) &&
+					!bms_overlap(check_attrs, extraUpdatedCols))
+				{
+					resultRelInfo->ri_UpdateSkipConstrCheck[i] = true;
+
+					ereport(DEBUG1,
+							errmsg_internal("skipping verification for constraint \"%s\" on table \"%s\"",
+											check[i].ccname,
+											RelationGetRelationName(rel)));
+				}
+			}
 		}
 		MemoryContextSwitchTo(oldContext);
 	}
@@ -1837,6 +1872,10 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 		 * is not to be treated as a failure.  Therefore, use ExecCheck not
 		 * ExecQual.
 		 */
+		if (operation == CMD_UPDATE &&
+			resultRelInfo->ri_UpdateSkipConstrCheck[i] == true)
+			continue;
+
 		if (checkconstr && !ExecCheck(checkconstr, econtext))
 			return check[i].ccname;
 	}
@@ -1977,7 +2016,7 @@ ExecPartitionCheckEmitError(ResultRelInfo *resultRelInfo,
  * 'resultRelInfo' is the final result relation, after tuple routing.
  */
 void
-ExecConstraints(ResultRelInfo *resultRelInfo,
+ExecConstraints(CmdType	operation, ResultRelInfo *resultRelInfo,
 				TupleTableSlot *slot, EState *estate)
 {
 	Relation	rel = resultRelInfo->ri_RelationDesc;
@@ -2027,7 +2066,7 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
 	{
 		const char *failed;
 
-		if ((failed = ExecRelCheck(resultRelInfo, slot, estate)) != NULL)
+		if ((failed = ExecRelCheck(operation, resultRelInfo, slot, estate)) != NULL)
 		{
 			char	   *val_desc;
 			Relation	orig_rel = rel;
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index def32774c90..a77ef6b26bd 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -835,7 +835,7 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo,
 
 		/* Check the constraints of the tuple */
 		if (rel->rd_att->constr)
-			ExecConstraints(resultRelInfo, slot, estate);
+			ExecConstraints(CMD_INSERT, resultRelInfo, slot, estate);
 		if (rel->rd_rel->relispartition)
 			ExecPartitionCheck(resultRelInfo, slot, estate, true);
 
@@ -932,7 +932,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
 
 		/* Check the constraints of the tuple */
 		if (rel->rd_att->constr)
-			ExecConstraints(resultRelInfo, slot, estate);
+			ExecConstraints(CMD_INSERT, resultRelInfo, slot, estate);
 		if (rel->rd_rel->relispartition)
 			ExecPartitionCheck(resultRelInfo, slot, estate, true);
 
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index e44f1223886..1f1951cfd34 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1094,7 +1094,7 @@ ExecInsert(ModifyTableContext *context,
 		 * Check the constraints of the tuple.
 		 */
 		if (resultRelationDesc->rd_att->constr)
-			ExecConstraints(resultRelInfo, slot, estate);
+			ExecConstraints(CMD_INSERT, resultRelInfo, slot, estate);
 
 		/*
 		 * Also check the tuple against the partition constraint, if there is
@@ -2291,7 +2291,7 @@ lreplace:
 	 * have it validate all remaining checks.
 	 */
 	if (resultRelationDesc->rd_att->constr)
-		ExecConstraints(resultRelInfo, slot, estate);
+		ExecConstraints(CMD_UPDATE, resultRelInfo, slot, estate);
 
 	/*
 	 * replace the heap tuple
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index f99fc26eb1f..378611488ae 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -257,7 +257,7 @@ extern void InitResultRelInfo(ResultRelInfo *resultRelInfo,
 extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid,
 											  ResultRelInfo *rootRelInfo);
 extern List *ExecGetAncestorResultRels(EState *estate, ResultRelInfo *resultRelInfo);
-extern void ExecConstraints(ResultRelInfo *resultRelInfo,
+extern void ExecConstraints(CmdType	operation, ResultRelInfo *resultRelInfo,
 							TupleTableSlot *slot, EState *estate);
 extern AttrNumber ExecRelGenVirtualNotNull(ResultRelInfo *resultRelInfo,
 										   TupleTableSlot *slot,
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 9018e190cc7..8a4878da48d 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -551,6 +551,13 @@ typedef struct ResultRelInfo
 	/* list of WithCheckOption expr states */
 	List	   *ri_WithCheckOptionExprs;
 
+	/*
+	 * ri_UpdateSkipConstrCheck[checkconstr - 1] is true if the UPDATE does not
+	 * touch any columns referenced by this check constraint. Except plain
+	 * UPDATE, This applys to ON CONFLICT DO UPDATE, MERGE UPDATE too.
+	 */
+	bool		*ri_UpdateSkipConstrCheck;
+
 	/* array of expr states for checking check constraints */
 	ExprState **ri_CheckConstraintExprs;
 
diff --git a/src/test/modules/test_misc/t/001_constraint_validation.pl b/src/test/modules/test_misc/t/001_constraint_validation.pl
index bdc751724f4..0e99e9381ad 100644
--- a/src/test/modules/test_misc/t/001_constraint_validation.pl
+++ b/src/test/modules/test_misc/t/001_constraint_validation.pl
@@ -40,6 +40,56 @@ sub is_table_verified
 
 my $output;
 
+note "test UPDATE operation skip enforced constraint vertification";
+# Check whether the run_sql_command output shows that the UPDATE operation
+# skipped constraint verification.
+sub is_update_constraint_skipped
+{
+	my $output = shift;
+	my $constr = shift;
+	return index($output, "DEBUG:  skipping verification for constraint \"$constr\"") != -1;
+}
+
+run_sql_command(
+	'CREATE TABLE upd_check_skip (
+	i int, a int default 11,
+	b int, c int,
+	d int generated always as (b+c) STORED,
+	e int generated always as (i) VIRTUAL) partition by range (i);
+
+	CREATE TABLE upd_check_skip_1(
+	a int default 12, i int,
+	c int, b int,
+	d int generated always as (b+1) STORED,
+	e int generated always as (b - 100) VIRTUAL);
+
+	ALTER TABLE upd_check_skip ATTACH PARTITION upd_check_skip_1 FOR VALUES FROM (0) TO (10);
+	CREATE TABLE upd_check_skip_2 PARTITION OF upd_check_skip FOR VALUES FROM (10) TO (30);
+	INSERT INTO upd_check_skip SELECT g + 8, g, -g-g, g+1 FROM generate_series(0, 7) g;
+	ALTER TABLE upd_check_skip ADD COLUMN f int DEFAULT 101;
+
+	ALTER TABLE upd_check_skip ADD CONSTRAINT cc1 CHECK(a+b < 1);
+	ALTER TABLE upd_check_skip ADD CONSTRAINT cc2 CHECK(a+c < 100);
+	ALTER TABLE upd_check_skip ADD CONSTRAINT cc3 CHECK(b < 1);
+	ALTER TABLE upd_check_skip ADD CONSTRAINT cc4 CHECK(d < 2); ');
+
+$output = run_sql_command('UPDATE upd_check_skip SET c = 3 WHERE i = 12;');
+ok(is_update_constraint_skipped($output, 'cc1'),
+	'UPDATE skipped verification for constraint cc1');
+ok(is_update_constraint_skipped($output, 'cc3'),
+	'UPDATE skipped verification for constraint cc3');
+
+$output = run_sql_command('UPDATE upd_check_skip SET b = -7 WHERE i = 11;');
+ok(is_update_constraint_skipped($output, 'cc2'),
+	'UPDATE skipped verification for constraint cc2');
+ok(!is_update_constraint_skipped($output, 'cc4'),
+	'UPDATE does not skipped verification for constraint cc4');
+
+$output = run_sql_command('UPDATE upd_check_skip SET f = 14 WHERE i = 15;');
+ok(is_update_constraint_skipped($output, 'cc4'),
+	'UPDATE skipped verification for constraint cc4');
+run_sql_command('drop table upd_check_skip;');
+
 note "test alter table set not null";
 
 run_sql_command(
-- 
2.34.1