UPDATE run check constraints for affected columns only

From 72bdfcf59c22e33c881ad650273a1949cb378d64 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sun, 7 Dec 2025 23:01:43 +0800
Subject: [PATCH v2 1/1] UPDATE run check constraints for affected columns only

commitfest: https://commitfest.postgresql.org/patch/6270
discussion: https://postgr.es/m/CACJufxEtY1hdLcx=Fhnqp-ERcV1PhbvELG5COy_CZjoEW76ZPQ@mail.gmail.com
context: https://postgr.es/m/1326055327.15293.13.camel%40vanquo.pezone.net
---
 src/backend/commands/copyfrom.c               |  2 +-
 src/backend/executor/execMain.c               | 53 +++++++++++--
 src/backend/executor/execReplication.c        |  4 +-
 src/backend/executor/nodeModifyTable.c        |  4 +-
 src/include/executor/executor.h               |  2 +-
 .../test_misc/t/001_constraint_validation.pl  | 77 +++++++++++++++++++
 6 files changed, 131 insertions(+), 11 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..7dac86a2ae5 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 cmdtype, ResultRelInfo *resultRelInfo,
 			 TupleTableSlot *slot, EState *estate)
 {
 	Relation	rel = resultRelInfo->ri_RelationDesc;
@@ -1800,11 +1801,20 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 	 */
 	if (resultRelInfo->ri_CheckConstraintExprs == NULL)
 	{
+		Bitmapset  *updatedCols;
+
+		if (cmdtype == CMD_UPDATE &&
+			!(rel->trigdesc && rel->trigdesc->trig_update_before_row))
+			updatedCols = ExecGetAllUpdatedCols(resultRelInfo, estate);
+		else
+			updatedCols = NULL;
+
 		oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
 		resultRelInfo->ri_CheckConstraintExprs = palloc0_array(ExprState *, ncheck);
 		for (int i = 0; i < ncheck; i++)
 		{
 			Expr	   *checkconstr;
+			bool		skip = false;
 
 			/* Skip not enforced constraint */
 			if (!check[i].ccenforced)
@@ -1812,8 +1822,41 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 
 			checkconstr = stringToNode(check[i].ccbin);
 			checkconstr = (Expr *) expand_generated_columns_in_expr((Node *) checkconstr, rel, 1);
-			resultRelInfo->ri_CheckConstraintExprs[i] =
-				ExecPrepareExpr(checkconstr, estate);
+
+			if (updatedCols)
+			{
+				Bitmapset  *check_attrs = NULL;
+
+				pull_varattnos((Node *) checkconstr, 1, &check_attrs);
+
+				/*
+				 * No Var reference and contain whole-row can not skip the
+				 * verification.
+				 */
+				if (check_attrs &&
+					!bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, check_attrs))
+				{
+					/*
+					 * If it's an update with a known set of update target
+					 * columns, see if we can skip the verification.
+					 */
+					if (!bms_overlap(check_attrs, updatedCols))
+					{
+						skip = true;
+
+						ereport(DEBUG1,
+								errmsg_internal("skipping verification for constraint \"%s\" on table \"%s\"",
+												check[i].ccname,
+												RelationGetRelationName(rel)));
+					}
+				}
+			}
+
+			if (!skip)
+			{
+				resultRelInfo->ri_CheckConstraintExprs[i] =
+					ExecPrepareExpr(checkconstr, estate);
+			}
 		}
 		MemoryContextSwitchTo(oldContext);
 	}
@@ -1977,7 +2020,7 @@ ExecPartitionCheckEmitError(ResultRelInfo *resultRelInfo,
  * 'resultRelInfo' is the final result relation, after tuple routing.
  */
 void
-ExecConstraints(ResultRelInfo *resultRelInfo,
+ExecConstraints(CmdType	cmdtype, ResultRelInfo *resultRelInfo,
 				TupleTableSlot *slot, EState *estate)
 {
 	Relation	rel = resultRelInfo->ri_RelationDesc;
@@ -2027,7 +2070,7 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
 	{
 		const char *failed;
 
-		if ((failed = ExecRelCheck(resultRelInfo, slot, estate)) != NULL)
+		if ((failed = ExecRelCheck(cmdtype, 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..3c3f2d67b73 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_UPDATE, 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 fa2b657fb2f..f61347208d6 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 cmdtype, ResultRelInfo *resultRelInfo,
 							TupleTableSlot *slot, EState *estate);
 extern AttrNumber ExecRelGenVirtualNotNull(ResultRelInfo *resultRelInfo,
 										   TupleTableSlot *slot,
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..36117ec824f 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,83 @@ 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 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 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 f = 14 WHERE i = 15;');
+ok(is_update_constraint_skipped($output, 'cc4'),
+	'UPDATE skipped verification for constraint cc4');
+
+run_sql_command(
+	'CREATE FUNCTION dummy_update_func() RETURNS trigger AS $$
+	 BEGIN
+	 RETURN NEW;
+	 END;
+	 $$ LANGUAGE plpgsql;
+
+	 CREATE TRIGGER upd_check_skip_row_trig_before
+	 BEFORE UPDATE ON upd_check_skip
+	 FOR EACH ROW
+	 EXECUTE PROCEDURE dummy_update_func(); ');
+
+$output = run_sql_command('UPDATE upd_check_skip SET f = f');
+
+ok(!is_update_constraint_skipped($output, 'cc1'),
+	'UPDATE does not skipped verification for constraint cc1');
+
+ok(!is_update_constraint_skipped($output, 'cc2'),
+	'UPDATE does not skipped verification for constraint cc2');
+
+ok(!is_update_constraint_skipped($output, 'cc3'),
+	'UPDATE does not skipped verification for constraint cc3');
+
+ok(!is_update_constraint_skipped($output, 'cc4'),
+	'UPDATE does not 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