On Mon, Dec 1, 2025 at 2:33 PM Tom Lane <[email protected]> wrote:
>
> jian he <[email protected]> writes:
> > The attached patch implements the $subject.
>
> Does this cover the case where a BEFORE UPDATE trigger has modified
> columns that were not mentioned in UPDATE...SET?
>
> regards, tom lane
hi.
in ExecInitGenerated, we have:
/*
* In an UPDATE, we can skip computing any generated 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 might change more
* columns.
*/
if (cmdtype == CMD_UPDATE &&
!(rel->trigdesc && rel->trigdesc->trig_update_before_row))
updatedCols = ExecGetUpdatedCols(resultRelInfo, estate);
else
updatedCols = NULL;
So I applied the equivalent approach. This should works fine, because if we are
able to skip computing certain generated columns, then we sure sure be able to
skip evaluating some check constraints.
--
jian
https://www.enterprisedb.com
From 72bdfcf59c22e33c881ad650273a1949cb378d64 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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_czjoew76...@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
