hi. while casually looking at https://wiki.postgresql.org/wiki/Todo then I found out this thread: https://postgr.es/m/1326055327.15293.13.camel%40vanquo.pezone.net
Seems easier to do nowadays. The attached patch implements the $subject. regress tests seems not enough to test it. Following the approach in 001_constraint_validation.pl, we use ereport(DEBUG1, errmsg_internal), then grep the logs to check whether the enforced constraint verification was skipped or not. we can not add check constraint to VIEW, tests covered partitioned table scarenio. DEMO: CREATE TABLE upd_check_skip (a int, b int, c int, d int generated always as (b+c) STORED); 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); INSERT INTO upd_check_skip DEFAULT VALUES; SET client_min_messages to DEBUG1; --constraint verification will be skipped for cc3, cc4 UPDATE upd_check_skip SET a = 1; --constraint verification will be skipped for cc2 UPDATE upd_check_skip SET b = -1; --constraint verification will be skipped for cc3 UPDATE upd_check_skip SET c = -1; -- jian https://www.enterprisedb.com
From 51dca341ee334a04e5dc296fe1da8af6851c6781 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> 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
