On Fri, Jan 23, 2026 at 10:09 AM jian he <[email protected]> wrote:
> code has been further simplified and is now more neat.
> The test is kind of verbose now.
>
if (check_attrs &&
!bms_is_member(-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)));
}
We need tests to reach the above ereport(DEBUG1, branch.
We can use
+SET log_statement to NONE;
+SET client_min_messages TO 'debug1';
to make sure the regress output has the DEBUG1 message.
--
jian
https://www.enterprisedb.com/
From d5a8d0d8cdc34b4f10a2bd98dcdaf54c49e99bb2 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 3 Mar 2026 23:00:59 +0800
Subject: [PATCH v4 1/1] skip unnecessary check constraint verification for
UPDATE
In an UPDATE, we can skip verification 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 might change more columns.
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 | 49 +++++++++++++++--
src/backend/executor/execReplication.c | 4 +-
src/backend/executor/nodeModifyTable.c | 4 +-
src/include/executor/executor.h | 2 +-
src/test/regress/expected/constraints.out | 65 ++++++++++++++++++++++
src/test/regress/sql/constraints.sql | 67 +++++++++++++++++++++++
7 files changed, 182 insertions(+), 11 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..e4ca2a0eb6f 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,18 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
*/
if (resultRelInfo->ri_CheckConstraintExprs == NULL)
{
+ Bitmapset *updatedCols = NULL;
+
+ /*
+ * During an UPDATE, we can skip CHECK constraint verification if the
+ * constraint expressions do not depend on any of the target columns.
+ * However, if a BEFORE ROW UPDATE trigger is present, we must perform
+ * the verification, 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);
for (int i = 0; i < ncheck; i++)
@@ -1816,8 +1829,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)
+ resultRelInfo->ri_CheckConstraintExprs[i] =
+ ExecPrepareExpr(checkconstr, estate);
+ else
+ {
+ Bitmapset *check_attrs = NULL;
+
+ pull_varattnos((Node *) checkconstr, 1, &check_attrs);
+
+ /*
+ * Check constraint verification cannot be skipped if the
+ * constraint expression contains a whole-row reference, or if
+ * it references any of the UPDATE target columns.
+ */
+ if (check_attrs &&
+ !bms_is_member(-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)));
+ }
+ else
+ resultRelInfo->ri_CheckConstraintExprs[i] =
+ ExecPrepareExpr(checkconstr, estate);
+ }
}
MemoryContextSwitchTo(oldContext);
}
@@ -1981,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;
@@ -2031,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 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..0fbb6b16fd7 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1721,3 +1721,68 @@ 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, 6) 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);
+SET log_statement to NONE;
+SET client_min_messages TO 'debug1';
+-- Check constraint expression contain whole-row reference therefore cannot be
+-- skipped
+UPDATE upd_check_skip0 SET a = 2;
+-- Skipping verification for check constraints cc2, since cc2 check expression
+-- does not reference column b
+UPDATE upd_check_skip SET b = null WHERE i = 8 OR i = 10;
+DEBUG: skipping verification for constraint "cc2" on table "upd_check_skip_1"
+DEBUG: skipping verification for constraint "cc2" on table "upd_check_skip_2"
+-- Skipping verification for check constraints cc1 and cc3 because column c is
+-- not referenced in their expressions. However, in table upd_check_skip_2, the
+-- generated column d references column c, thus check constraint cc4 on
+-- upd_check_skip_2 cannot be skipped
+UPDATE upd_check_skip SET c = 3 WHERE i = 12 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 "cc1" on table "upd_check_skip_2"
+DEBUG: skipping verification for constraint "cc3" on table "upd_check_skip_2"
+-- Skipping verification for all check constraints since column f is not
+-- referenced in any check constraint expression
+UPDATE upd_check_skip SET f = 14 WHERE i = 13;
+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"
+CREATE TRIGGER upd_check_skip_row_trig_before
+BEFORE UPDATE ON upd_check_skip
+FOR EACH ROW
+EXECUTE PROCEDURE dummy_update_func_in_constr();
+-- Cannot skip check constraint validation for this UPDATE because a trigger
+-- prevents it
+UPDATE upd_check_skip SET a = NULL, b = NULL, c = NULL, i = NULL;
+ERROR: no partition of relation "upd_check_skip" found for row
+DETAIL: Partition key of the failing row contains (i) = (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..1706bf57f57 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -1062,3 +1062,70 @@ 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, 6) 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);
+
+SET log_statement to NONE;
+SET client_min_messages TO 'debug1';
+
+-- Check constraint expression contain whole-row reference therefore cannot be
+-- skipped
+UPDATE upd_check_skip0 SET a = 2;
+
+-- Skipping verification for check constraints cc2, since cc2 check expression
+-- does not reference column b
+UPDATE upd_check_skip SET b = null WHERE i = 8 OR i = 10;
+
+-- Skipping verification for check constraints cc1 and cc3 because column c is
+-- not referenced in their expressions. However, in table upd_check_skip_2, the
+-- generated column d references column c, thus check constraint cc4 on
+-- upd_check_skip_2 cannot be skipped
+UPDATE upd_check_skip SET c = 3 WHERE i = 12 OR i = 8;
+
+-- Skipping verification for all check constraints since column f is not
+-- referenced in any check constraint expression
+UPDATE upd_check_skip SET f = 14 WHERE i = 13;
+
+CREATE TRIGGER upd_check_skip_row_trig_before
+BEFORE UPDATE ON upd_check_skip
+FOR EACH ROW
+EXECUTE PROCEDURE dummy_update_func_in_constr();
+
+-- Cannot skip check constraint validation for this UPDATE because a trigger
+-- prevents it
+UPDATE upd_check_skip SET a = NULL, b = NULL, c = NULL, i = 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
