v6-0001-skip-unnecessary-check-constraint-verification-for-UPDATE.patch

From 88e8da4782a7cee9e07fee256e09494267ef60b6 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 6 Mar 2026 10:15:49 +0800
Subject: [PATCH v6 1/1] skip unnecessary check constraint verification for
 UPDATE

In an UPDATE, we can skip verification of any columns that do not depend on any
UPDATE target column.  But if there is a BEFORE ROW UPDATE trigger, we cannot
skip because the trigger may modifies more column.

We cache ri_CheckConstraintExprs in ExecConstraints and must initialize them all
if different actions like INSERT and UPDATE, happen together in a query.
Because both INSERT and UPDATE need to use these ri_CheckConstraintExprs.
Invoke INSERT AND UPDATE together will happen within MERGE command.  We confirm
it's a MERGE operation by checking that resultRelInfo->ri_MergeActions is not
NIL. See ExecMergeNotMatched and ExecMergeMatched.

For cross-partition updates (ExecCrossPartitionUpdate), the operation splits
into an INSERT and DELETE, we can treat it as a single action.

Author: jian he <jian.universality@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: li carol <carol.li2025@outlook.com>
Reviewed-by: Jacob Champion <jacob.champion@enterprisedb.com>
Reviewed-by: Florin

Discussion: https://postgr.es/m/CACJufxEtY1hdLcx=Fhnqp-ERcV1PhbvELG5COy_CZjoEW76ZPQ@mail.gmail.com
context: https://postgr.es/m/1326055327.15293.13.camel%40vanquo.pezone.net
commitfest: https://commitfest.postgresql.org/patch/6270
---
 src/backend/commands/copyfrom.c           |  2 +-
 src/backend/executor/execMain.c           | 54 ++++++++++++++-
 src/backend/executor/execReplication.c    |  4 +-
 src/backend/executor/nodeModifyTable.c    |  4 +-
 src/include/executor/executor.h           |  2 +-
 src/test/regress/expected/constraints.out | 80 +++++++++++++++++++++++
 src/test/regress/sql/constraints.sql      | 73 +++++++++++++++++++++
 7 files changed, 210 insertions(+), 9 deletions(-)

diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 2f42f55e229..43354a3c454 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1356,7 +1356,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 bfd3ebc601e..851073f2bc0 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"
@@ -1779,7 +1780,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;
@@ -1804,6 +1805,25 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 	 */
 	if (resultRelInfo->ri_CheckConstraintExprs == NULL)
 	{
+		Bitmapset  *updatedCols = NULL;
+
+		/*
+		 * During an UPDATE, we may skip CHECK constraint verification.
+		 * However, if a BEFORE ROW UPDATE trigger is present, we must perform
+		 * the verification, as the trigger might modify additional columns.
+		 * We cannot skip this if the UPDATE is from MERGE.
+		 */
+		if (cmdtype == CMD_UPDATE &&
+			!(rel->trigdesc && rel->trigdesc->trig_update_before_row))
+		{
+			if (resultRelInfo->ri_MergeActions[MERGE_WHEN_MATCHED] == NIL &&
+				resultRelInfo->ri_MergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] == NIL &&
+				resultRelInfo->ri_MergeActions[MERGE_WHEN_NOT_MATCHED_BY_TARGET] == NIL)
+			{
+				updatedCols = ExecGetAllUpdatedCols(resultRelInfo, estate);
+			}
+		}
+
 		oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
 		resultRelInfo->ri_CheckConstraintExprs = palloc0_array(ExprState *, ncheck);
 		for (int i = 0; i < ncheck; i++)
@@ -1816,6 +1836,34 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 
 			checkconstr = stringToNode(check[i].ccbin);
 			checkconstr = (Expr *) expand_generated_columns_in_expr((Node *) checkconstr, rel, 1);
+
+			if (updatedCols)
+			{
+				Bitmapset  *check_attrs = NULL;
+
+				pull_varattnos((Node *) checkconstr, 1, &check_attrs);
+
+				/*
+				 * Skip check constraint verification during an UPDATE if the
+				 * constraint expression references columns, contains no
+				 * whole-row references, and does not reference any of the
+				 * columns being updated.
+				 */
+				if (check_attrs &&
+					!bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, check_attrs) &&
+					!bms_overlap(check_attrs, updatedCols))
+				{
+					ereport(DEBUG1,
+							errmsg_internal("skipping verification for constraint \"%s\" on table \"%s\"",
+											check[i].ccname,
+											RelationGetRelationName(rel)));
+
+					bms_free(check_attrs);
+
+					continue;
+				}
+			}
+
 			resultRelInfo->ri_CheckConstraintExprs[i] =
 				ExecPrepareExpr(checkconstr, estate);
 		}
@@ -1981,7 +2029,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;
@@ -2031,7 +2079,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 2497ee7edc5..157a21c1da3 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -836,7 +836,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);
 
@@ -940,7 +940,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 793c76d4f82..95969dbadb4 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1101,7 +1101,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
@@ -2316,7 +2316,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 d46ba59895d..0b1438d3f7c 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/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index a6fa9cacb72..9f3bb0e0d89 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1721,3 +1721,83 @@ COMMENT ON CONSTRAINT inv_ck ON DOMAIN constraint_comments_dom IS 'comment on in
 CREATE TABLE regress_notnull1 (a integer);
 CREATE TABLE regress_notnull2 () INHERITS (regress_notnull1);
 ALTER TABLE ONLY regress_notnull2 ALTER COLUMN a SET NOT NULL;
+CREATE FUNCTION dummy_update_func_in_constr() RETURNS trigger AS $$
+BEGIN
+RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+CREATE TABLE upd_check_skip0 (a int, CONSTRAINT cc CHECK (upd_check_skip0 IS NOT NULL));
+INSERT INTO upd_check_skip0 VALUES (1);
+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, 4) g;
+ALTER TABLE upd_check_skip ADD COLUMN f int;
+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);
+ALTER TABLE upd_check_skip ADD CONSTRAINT cc5 CHECK (e < 20);
+SELECT * FROM upd_check_skip ORDER BY i;
+ i  | a | b  | c | d  | e  | f 
+----+---+----+---+----+----+---
+  8 | 0 |  0 | 1 |  1 |  8 |  
+  9 | 1 | -2 | 2 | -1 |  9 |  
+ 10 | 2 | -4 | 3 | -1 | 10 |  
+ 11 | 3 | -6 | 4 | -2 | 11 |  
+ 12 | 4 | -8 | 5 | -3 | 12 |  
+(5 rows)
+
+SET log_statement to NONE;
+SET client_min_messages TO 'debug1';
+-- constraint cc contain whole-row reference therefore cannot be skipped
+UPDATE upd_check_skip0 SET a = 2;
+-- error, but cc2, cc5 are still being skipped
+UPDATE upd_check_skip SET b = 11, d = default WHERE i = 12;
+DEBUG:  skipping verification for constraint "cc2" on table "upd_check_skip_2"
+DEBUG:  skipping verification for constraint "cc5" on table "upd_check_skip_2"
+ERROR:  new row for relation "upd_check_skip_2" violates check constraint "cc1"
+DETAIL:  Failing row contains (12, 4, 11, 5, 16, virtual, null).
+-- skipping verification for constraint cc1, cc3, cc5 because column c is not
+-- being referenced. However, in table upd_check_skip_2, the generated column d
+-- references column c, therefore constraint cc4 on upd_check_skip_2 cannot be skipped
+UPDATE upd_check_skip SET c = 3 WHERE i = 11 OR i = 8;
+DEBUG:  skipping verification for constraint "cc1" on table "upd_check_skip_1"
+DEBUG:  skipping verification for constraint "cc3" on table "upd_check_skip_1"
+DEBUG:  skipping verification for constraint "cc4" on table "upd_check_skip_1"
+DEBUG:  skipping verification for constraint "cc5" on table "upd_check_skip_1"
+DEBUG:  skipping verification for constraint "cc1" on table "upd_check_skip_2"
+DEBUG:  skipping verification for constraint "cc3" on table "upd_check_skip_2"
+DEBUG:  skipping verification for constraint "cc5" on table "upd_check_skip_2"
+-- column f is not referenced by any constraints
+-- virtual generated column is expanded before constraint check
+UPDATE upd_check_skip SET f = 14, e = DEFAULT WHERE i = 12;
+DEBUG:  skipping verification for constraint "cc1" on table "upd_check_skip_2"
+DEBUG:  skipping verification for constraint "cc2" on table "upd_check_skip_2"
+DEBUG:  skipping verification for constraint "cc3" on table "upd_check_skip_2"
+DEBUG:  skipping verification for constraint "cc4" on table "upd_check_skip_2"
+DEBUG:  skipping verification for constraint "cc5" on table "upd_check_skip_2"
+-- cross partition update cannot be skipped
+UPDATE upd_check_skip SET f = 14, i = 9 WHERE i = 12;
+-- MERGE UPDATE cannot be skipped
+MERGE INTO upd_check_skip t USING (VALUES (8, 2)) AS s(a, b)
+  ON t.i = s.a WHEN MATCHED THEN UPDATE SET f = s.b;
+CREATE TRIGGER upd_check_skip_row_trig_before
+BEFORE UPDATE ON upd_check_skip
+FOR EACH ROW
+EXECUTE PROCEDURE dummy_update_func_in_constr();
+-- BEFORE ROW UPDATE TRIGGER is there, cannot skip
+UPDATE upd_check_skip SET a = NULL, b = NULL, c = NULL;
+DROP TRIGGER upd_check_skip_row_trig_before ON upd_check_skip;
+DROP FUNCTION dummy_update_func_in_constr;
+DROP TABLE upd_check_skip0;
+DROP TABLE upd_check_skip;
+RESET client_min_messages;
+RESET log_statement;
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index b7f6efdd814..d1e4d7e5962 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -1062,3 +1062,76 @@ COMMENT ON CONSTRAINT inv_ck ON DOMAIN constraint_comments_dom IS 'comment on in
 CREATE TABLE regress_notnull1 (a integer);
 CREATE TABLE regress_notnull2 () INHERITS (regress_notnull1);
 ALTER TABLE ONLY regress_notnull2 ALTER COLUMN a SET NOT NULL;
+
+CREATE FUNCTION dummy_update_func_in_constr() RETURNS trigger AS $$
+BEGIN
+RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TABLE upd_check_skip0 (a int, CONSTRAINT cc CHECK (upd_check_skip0 IS NOT NULL));
+INSERT INTO upd_check_skip0 VALUES (1);
+
+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, 4) g;
+ALTER TABLE upd_check_skip ADD COLUMN f int;
+
+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);
+ALTER TABLE upd_check_skip ADD CONSTRAINT cc5 CHECK (e < 20);
+
+SELECT * FROM upd_check_skip ORDER BY i;
+
+SET log_statement to NONE;
+SET client_min_messages TO 'debug1';
+
+-- constraint cc contain whole-row reference therefore cannot be skipped
+UPDATE upd_check_skip0 SET a = 2;
+
+-- error, but cc2, cc5 are still being skipped
+UPDATE upd_check_skip SET b = 11, d = default WHERE i = 12;
+
+-- skipping verification for constraint cc1, cc3, cc5 because column c is not
+-- being referenced. However, in table upd_check_skip_2, the generated column d
+-- references column c, therefore constraint cc4 on upd_check_skip_2 cannot be skipped
+UPDATE upd_check_skip SET c = 3 WHERE i = 11 OR i = 8;
+
+-- column f is not referenced by any constraints
+-- virtual generated column is expanded before constraint check
+UPDATE upd_check_skip SET f = 14, e = DEFAULT WHERE i = 12;
+
+-- cross partition update cannot be skipped
+UPDATE upd_check_skip SET f = 14, i = 9 WHERE i = 12;
+
+-- MERGE UPDATE cannot be skipped
+MERGE INTO upd_check_skip t USING (VALUES (8, 2)) AS s(a, b)
+  ON t.i = s.a WHEN MATCHED THEN UPDATE SET f = s.b;
+
+CREATE TRIGGER upd_check_skip_row_trig_before
+BEFORE UPDATE ON upd_check_skip
+FOR EACH ROW
+EXECUTE PROCEDURE dummy_update_func_in_constr();
+
+-- BEFORE ROW UPDATE TRIGGER is there, cannot skip
+UPDATE upd_check_skip SET a = NULL, b = NULL, c = NULL;
+
+DROP TRIGGER upd_check_skip_row_trig_before ON upd_check_skip;
+DROP FUNCTION dummy_update_func_in_constr;
+DROP TABLE upd_check_skip0;
+DROP TABLE upd_check_skip;
+
+RESET client_min_messages;
+RESET log_statement;
-- 
2.34.1