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

From 2cdf1d47faa8a29c6b363335895488834a76620e Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sat, 7 Mar 2026 17:35:24 +0800
Subject: [PATCH v7 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 Irion <irionr@gmail.com>
Reviewed-by: Haritabh Gupta <haritabh1992@gmail.com>

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           |  69 +++++++++++++--
 src/backend/executor/execReplication.c    |   4 +-
 src/backend/executor/nodeModifyTable.c    |   4 +-
 src/include/executor/executor.h           |   2 +-
 src/include/nodes/execnodes.h             |   7 +-
 src/test/regress/expected/constraints.out | 103 ++++++++++++++++++++++
 src/test/regress/sql/constraints.sql      |  85 ++++++++++++++++++
 8 files changed, 259 insertions(+), 17 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..f29f50afd71 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"
@@ -1292,7 +1293,8 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
 	resultRelInfo->ri_projectNewInfoValid = false;
 	resultRelInfo->ri_FdwState = NULL;
 	resultRelInfo->ri_usesFdwDirectModify = false;
-	resultRelInfo->ri_CheckConstraintExprs = NULL;
+	resultRelInfo->ri_CheckConstraintExprsI = NULL;
+	resultRelInfo->ri_CheckConstraintExprsU = NULL;
 	resultRelInfo->ri_GenVirtualNotNullConstraintExprs = NULL;
 	resultRelInfo->ri_GeneratedExprsI = NULL;
 	resultRelInfo->ri_GeneratedExprsU = NULL;
@@ -1779,7 +1781,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;
@@ -1797,15 +1799,36 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 		elog(ERROR, "%d pg_constraint record(s) missing for relation \"%s\"",
 			 rel->rd_rel->relchecks - ncheck, RelationGetRelationName(rel));
 
+	Assert(cmdtype == CMD_UPDATE || cmdtype == CMD_INSERT);
+
 	/*
 	 * If first time through for this result relation, build expression
 	 * nodetrees for rel's constraint expressions.  Keep them in the per-query
 	 * memory context so they'll survive throughout the query.
 	 */
-	if (resultRelInfo->ri_CheckConstraintExprs == NULL)
+	if ((!resultRelInfo->ri_CheckConstraintExprsU && cmdtype == CMD_UPDATE) ||
+		(!resultRelInfo->ri_CheckConstraintExprsI && cmdtype == CMD_INSERT))
 	{
+		Bitmapset  *updatedCols = NULL;
+		ExprState **checkExprs;
+
+		/*
+		 * During an UPDATE, we may skip CHECK constraint verification. But if
+		 * BEFORE ROW UPDATE trigger is present, the verification cannot be
+		 * skipped as the trigger might modify additional columns
+		 */
+		if (cmdtype == CMD_UPDATE &&
+			!(rel->trigdesc && rel->trigdesc->trig_update_before_row))
+			updatedCols = ExecGetAllUpdatedCols(resultRelInfo, estate);
+
 		oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
-		resultRelInfo->ri_CheckConstraintExprs = palloc0_array(ExprState *, ncheck);
+		checkExprs = palloc0_array(ExprState *, ncheck);
+
+		if (cmdtype == CMD_UPDATE)
+			resultRelInfo->ri_CheckConstraintExprsU = checkExprs;
+		else
+			resultRelInfo->ri_CheckConstraintExprsI = checkExprs;
+
 		for (int i = 0; i < ncheck; i++)
 		{
 			Expr	   *checkconstr;
@@ -1816,8 +1839,34 @@ 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);
+
+				/*
+				 * Skip check constraint verification during an UPDATE if the
+				 * constraint expression references any 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;
+				}
+			}
+			checkExprs[i] = ExecPrepareExpr(checkconstr, estate);
 		}
 		MemoryContextSwitchTo(oldContext);
 	}
@@ -1834,7 +1883,9 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 	/* And evaluate the constraints */
 	for (int i = 0; i < ncheck; i++)
 	{
-		ExprState  *checkconstr = resultRelInfo->ri_CheckConstraintExprs[i];
+		ExprState  *checkconstr = (cmdtype == CMD_INSERT)
+			? resultRelInfo->ri_CheckConstraintExprsI[i]
+			: resultRelInfo->ri_CheckConstraintExprsU[i];
 
 		/*
 		 * NOTE: SQL specifies that a NULL result from a constraint expression
@@ -1981,7 +2032,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 +2082,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/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 63c067d5aae..45b321faac3 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -554,8 +554,11 @@ typedef struct ResultRelInfo
 	/* list of WithCheckOption expr states */
 	List	   *ri_WithCheckOptionExprs;
 
-	/* array of expr states for checking check constraints */
-	ExprState **ri_CheckConstraintExprs;
+	/*
+	 * Arrays of check constraint ExprStates for INSERT/UPDATE/MERGE.
+	 */
+	ExprState **ri_CheckConstraintExprsI;
+	ExprState **ri_CheckConstraintExprsU;
 
 	/*
 	 * array of expr states for checking not-null constraints on virtual
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index a6fa9cacb72..99e2cd6ff4a 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1721,3 +1721,106 @@ 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).
+-- error, but cc2, cc5 are still being skipped
+MERGE INTO upd_check_skip t USING (VALUES (12, 11)) AS s(a, b)
+  ON t.i = s.a WHEN MATCHED THEN UPDATE SET b = s.b, d = default;
+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).
+-- MERGE UPDATE referenced constraint will be skipped, but MERGE INSERT
+-- constraint check still need enforced
+MERGE INTO upd_check_skip t USING (VALUES (12, -5), (18, 0)) AS s(a, b)
+  ON t.i = s.a
+  WHEN MATCHED THEN UPDATE SET b = s.b
+  WHEN NOT MATCHED BY TARGET THEN INSERT(i, a, b, c) VALUES (12, -3, -1, 4);
+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 "cc4"
+DETAIL:  Failing row contains (12, -3, -1, 4, 3, virtual, null).
+MERGE INTO upd_check_skip t USING (VALUES (8, 0), (18, 0)) AS s(a, b)
+  ON t.i = s.a
+  WHEN MATCHED THEN UPDATE SET b = s.b
+  WHEN NOT MATCHED BY SOURCE AND i = 11 THEN UPDATE SET a = -3, b = -1, c = 4;
+DEBUG:  skipping verification for constraint "cc5" on table "upd_check_skip_2"
+ERROR:  new row for relation "upd_check_skip_2" violates check constraint "cc4"
+DETAIL:  Failing row contains (11, -3, -1, 4, 3, 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"
+-- test cross partition update
+UPDATE upd_check_skip SET b = 11, i = 9 WHERE i = 12;
+ERROR:  new row for relation "upd_check_skip_1" violates check constraint "cc1"
+DETAIL:  Failing row contains (9, 4, 11, 5, 12, virtual, 14).
+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..37b23aa4b81 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -1062,3 +1062,88 @@ 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;
+
+-- error, but cc2, cc5 are still being skipped
+MERGE INTO upd_check_skip t USING (VALUES (12, 11)) AS s(a, b)
+  ON t.i = s.a WHEN MATCHED THEN UPDATE SET b = s.b, d = default;
+
+-- MERGE UPDATE referenced constraint will be skipped, but MERGE INSERT
+-- constraint check still need enforced
+MERGE INTO upd_check_skip t USING (VALUES (12, -5), (18, 0)) AS s(a, b)
+  ON t.i = s.a
+  WHEN MATCHED THEN UPDATE SET b = s.b
+  WHEN NOT MATCHED BY TARGET THEN INSERT(i, a, b, c) VALUES (12, -3, -1, 4);
+
+MERGE INTO upd_check_skip t USING (VALUES (8, 0), (18, 0)) AS s(a, b)
+  ON t.i = s.a
+  WHEN MATCHED THEN UPDATE SET b = s.b
+  WHEN NOT MATCHED BY SOURCE AND i = 11 THEN UPDATE SET a = -3, b = -1, c = 4;
+
+-- 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;
+
+-- test cross partition update
+UPDATE upd_check_skip SET b = 11, i = 9 WHERE i = 12;
+
+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