On Sat, Mar 7, 2026 at 12:10 AM Florin Irion <[email protected]> wrote: > > Hi Jian, > > An alternative that preserves the optimization for MERGE UPDATE actions > would be to follow the existing ri_GeneratedExprsI/ri_GeneratedExprsU > pattern — split into two separate cached arrays: > > ``` > /* array of expr states for checking check constraints */ > ExprState **ri_CheckConstraintExprsI; /* for INSERT */ > ExprState **ri_CheckConstraintExprsU; /* for UPDATE */ > ```
I have given it a try, please see attached v7. I don’t have a strong preference for either v6 or v7. > 2. The cross-partition update test comment says "cannot be skipped", but > cross-partition UPDATE goes through ExecCrossPartitionUpdate which > does DELETE + INSERT. The constraint check happens via ExecInsert with > CMD_INSERT on the destination partition, so the optimization was never > applicable. The test doesn't exercise anything specific to this patch. > 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 -- jian https://www.enterprisedb.com/
From 2cdf1d47faa8a29c6b363335895488834a76620e Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> 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 <[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 | 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
