On Sat, Mar 7, 2026 at 5:46 PM jian he <[email protected]> wrote: > > OK. I kept these tests. I think covering this scenario is useful. Perhaps it > has already been tested elsewhere, but including it here makes the tests more > complete >
+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; In V7, setting client_min_messages to capture DEBUG1 messages in regression tests is not a good idea, as other messages may also be emitted. So I added some tests on test/modules/test_misc/t/001_constraint_validation.pl. I also made some cosmetic changes to make ExecRelCheck more readable. -- jian https://www.enterprisedb.com/
From 482db334dcac0aeb1b2d4ec4c992e709150568f9 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 2 Sep 2026 09:49:24 +0800 Subject: [PATCH v8 1/1] skip unnecessary check constraint verification for UPDATE If an UPDATE does not modify any column referenced by a CHECK constraint, the new tuple satisfies the constraint whenever the old tuple did, so re-verifying it is unnecessary. Verification cannot be skipped if: * a BEFORE ROW UPDATE trigger exists, since it may modify columns beyond the UPDATE target list * the constraint is NOT VALID since pre-existing rows may violate it * the constraint contains a whole-row reference, or references no columns at all. * the constraint is NOT ENFORCED Columns modified indirectly through stored generated columns count as updated, per ExecGetAllUpdatedCols. Author: jian he <[email protected]> Reviewed-by: Tom Lane <[email protected]> Reviewed-by: li carol <[email protected]> Reviewed-by: Jacob Champion <[email protected]> Reviewed-by: Florin Irion <[email protected]> Reviewed-by: Haritabh Gupta <[email protected]> Discussion: https://postgr.es/m/CACJufxEtY1hdLcx=fhnqp-ercv1phbvelg5coy_czjoew76...@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 | 80 +++++++++++++++-- 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 | 88 +++++++++++++++++++ 7 files changed, 170 insertions(+), 17 deletions(-) diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index 3782db171be..169afa71a90 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -1357,7 +1357,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 6e47856cf25..a47efb3e49f 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -54,6 +54,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" @@ -1353,7 +1354,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; @@ -1857,7 +1859,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; @@ -1865,6 +1867,7 @@ ExecRelCheck(ResultRelInfo *resultRelInfo, ConstrCheck *check = rel->rd_att->constr->check; ExprContext *econtext; MemoryContext oldContext; + ExprState **checkExprs; /* * CheckNNConstraintFetch let this pass with only a warning, but now we @@ -1875,15 +1878,35 @@ ExecRelCheck(ResultRelInfo *resultRelInfo, elog(ERROR, "%d pg_constraint record(s) missing for relation \"%s\"", rel->rd_rel->relchecks - ncheck, RelationGetRelationName(rel)); + if (cmdtype != CMD_INSERT && cmdtype != CMD_UPDATE) + elog(ERROR, "unexpected command type: %d", (int) cmdtype); + + if (cmdtype == CMD_INSERT) + checkExprs = resultRelInfo->ri_CheckConstraintExprsI; + else + checkExprs = resultRelInfo->ri_CheckConstraintExprsU; + /* * 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 (!checkExprs) { + Bitmapset *updatedCols = NULL; + + /* + * 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); + for (int i = 0; i < ncheck; i++) { Expr *checkconstr; @@ -1894,9 +1917,48 @@ 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) + { + bool skip = false; + Bitmapset *check_attrs = NULL; + + pull_varattnos((Node *) checkconstr, 1, &check_attrs); + + /* + * Skip verification of a check constraint during UPDATE if it + * is validated, references at least one column, contains no + * whole-row reference, and references none of the columns + * being updated: the unchanged columns already satisfied the + * constraint in the old tuple, so the new tuple satisfies it + * too. + */ + skip = check_attrs && + check[i].ccvalid && + !bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber, check_attrs) && + !bms_overlap(check_attrs, updatedCols); + + bms_free(check_attrs); + + if (skip) + { + ereport(DEBUG1, + errmsg_internal("skipping verification for constraint \"%s\" on table \"%s\"", + check[i].ccname, + RelationGetRelationName(rel))); + + checkExprs[i] = NULL; + continue; + } + } + checkExprs[i] = ExecPrepareExpr(checkconstr, estate); } + + if (cmdtype == CMD_INSERT) + resultRelInfo->ri_CheckConstraintExprsI = checkExprs; + else + resultRelInfo->ri_CheckConstraintExprsU = checkExprs; + MemoryContextSwitchTo(oldContext); } @@ -1912,7 +1974,7 @@ ExecRelCheck(ResultRelInfo *resultRelInfo, /* And evaluate the constraints */ for (int i = 0; i < ncheck; i++) { - ExprState *checkconstr = resultRelInfo->ri_CheckConstraintExprs[i]; + ExprState *checkconstr = checkExprs[i]; /* * NOTE: SQL specifies that a NULL result from a constraint expression @@ -2059,7 +2121,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; @@ -2109,7 +2171,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 b2ca5cbf117..bdfb1356c3c 100644 --- a/src/backend/executor/execReplication.c +++ b/src/backend/executor/execReplication.c @@ -840,7 +840,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); @@ -944,7 +944,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 3056b850f73..d8310ee4d48 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -1115,7 +1115,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 @@ -2577,7 +2577,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 190e8a4897a..e43173c1f46 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -258,7 +258,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 f0cb21444b2..08d3cd6d75b 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -584,8 +584,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/modules/test_misc/t/001_constraint_validation.pl b/src/test/modules/test_misc/t/001_constraint_validation.pl index 6121c5bcae5..93cb8577dc1 100644 --- a/src/test/modules/test_misc/t/001_constraint_validation.pl +++ b/src/test/modules/test_misc/t/001_constraint_validation.pl @@ -333,6 +333,94 @@ like( 'updated partition constraint for default partition quuux_default1'); run_sql_command('DROP TABLE quuux;'); +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_constraint_skipped_by_update +{ + 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; + + 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_constraint_skipped_by_update($output, 'cc2'), + 'UPDATE skipped verification for constraint cc2'); +ok(!is_constraint_skipped_by_update($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_constraint_skipped_by_update($output, 'cc1'), + 'UPDATE skipped verification for constraint cc1'); +ok(is_constraint_skipped_by_update($output, 'cc3'), + 'UPDATE skipped verification for constraint cc3'); +ok(!is_constraint_skipped_by_update($output, 'cc4'), + 'UPDATE does not skipped verification for constraint cc4'); + +$output = run_sql_command('UPDATE upd_check_skip SET f = 14 WHERE i = 13;'); +ok(is_constraint_skipped_by_update($output, 'cc4'), + 'UPDATE skipped verification for constraint cc4'); + +$output = run_sql_command(' + 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, 2);'); +ok(is_constraint_skipped_by_update($output, 'cc2'), + 'UPDATE skipped verification for constraint cc2'); + +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 = NULL'); + +ok(!is_constraint_skipped_by_update($output, 'cc1'), + 'UPDATE does not skipped verification for constraint cc1'); + +ok(!is_constraint_skipped_by_update($output, 'cc2'), + 'UPDATE does not skipped verification for constraint cc2'); + +ok(!is_constraint_skipped_by_update($output, 'cc3'), + 'UPDATE does not skipped verification for constraint cc3'); + +ok(!is_constraint_skipped_by_update($output, 'cc4'), + 'UPDATE does not skipped verification for constraint cc4'); + +run_sql_command('drop table upd_check_skip;'); + $node->stop('fast'); done_testing(); -- 2.34.1
