public inbox for [email protected]
help / color / mirror / Atom feedFrom: Paul A Jungwirth <[email protected]>
To: SATYANARAYANA NARLAPURAM <[email protected]>
Cc: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column
Date: Wed, 15 Apr 2026 13:59:21 -0700
Message-ID: <CA+renyWD+XXifwswE74vhjooqbiVKu4qVhLvpMcUQBzrjVjT7A@mail.gmail.com> (raw)
In-Reply-To: <CAHg+QDeGLfz8YSCChjqrxaVSrz9AnMA0NrmsNogLqeGgCt7-wg@mail.gmail.com>
References: <CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com>
<CACJufxGreOtA-S-qeHyS5iSSsj5zZX0W3Rf8FxbyL+SVXFjLYw@mail.gmail.com>
<CAHg+QDeGLfz8YSCChjqrxaVSrz9AnMA0NrmsNogLqeGgCt7-wg@mail.gmail.com>
On Fri, Apr 10, 2026 at 3:01 PM SATYANARAYANA NARLAPURAM
<[email protected]> wrote:
>
>> I've combined all these changes into a single patch for now, as they
>> seem closely related.
>>
>> [1]: https://postgr.es/m/CACJufxHALFKca5SMn5DNnbrX2trPamVL6napn_nm35p15yw+rg@mail.gmail.com
>
> I applied your patch and tested. The following scenarios are now passing: (1) table inheritance issue I reported in [1], (2) issue reported in this thread.
They look good to me too. I read through the patch and made some
stylistic changes, as well as some grammar/typo fixes. In
ExecInitForPortionOf I tried to group things for each case more
closely, instead of separate disconnected branches. I also added/moved
some comments. Please see the attached.
There's something I think we could still improve: we omit the
valid-time in updatedCols, since that bitmapset is for permissions
checking (at least originally), but now other features are using it as
well. Our fix adds special logic to consider the valid-at column for
GENERATED column dependencies and more special logic for UPDATE OF
triggers. Perhaps we should add the attno to updatedCols after all,
and put the special logic in the permissions check instead? That seems
simpler and more robust. Or maybe it's time to have separate
bitmapsets, one for permissions and another for everything else? What
do you think?
I'm not sure we should be consolidating these fixes into one patch.
But I tried to call out all the issues in my commit message.
> Following are still failing:
>
> (1) instead of triggers + views, mentioned in the thread [2], it has both the test case and the fix.
I'll follow up there.
> (2) For Portion Of DELETE loses rows when a BEFORE INSERT trigger returns NULL
>
> ...
>
> (3) FPO UPDATE loses leftovers the same way
I agree with jian that this is the correct behavior. The inserts are
supposed to fire triggers, and if a trigger signals to cancel the
insert, that's what we should do.
Yours,
--
Paul ~{:-)
[email protected]
Attachments:
[application/octet-stream] v3-0001-Fix-some-problems-with-UPDATE-FOR-PORTION-OF.patch (22.3K, 2-v3-0001-Fix-some-problems-with-UPDATE-FOR-PORTION-OF.patch)
download | inline diff:
From 1b705168a9bbcf71464a158cc84f47b9e50dde3a Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 10 Apr 2026 17:01:12 +0800
Subject: [PATCH v3] Fix some problems with UPDATE FOR PORTION OF
- Fixed inserting leftovers with traditional table inheritance. Since there is
no tuple routing, we must add them directly to the child table. Also this
preserves extra columns in that table.
- Added ExecInitForPortionOf. This sets up executor state for child partitions.
Previously we did this in ExecForPortionOfLeftovers, but doing it earlier lets
us use the child->parent attr mapping in the fixes below.
- Made sure GENERATED STORED columns that depend on the application-time column
get updated. We exclude that column from the updatedCols bitmapset, because it
does not require permissions. But then we must remember to consider it later.
- Made a similar fix for UPDATE OF triggers.
- Clarified a comment about the rangetype stored in ForPortionOfState.
Discussion: https://postgr.es/m/CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com
Discussion: https://postgr.es/m/CAHg+QDcsXsUVaZ+JwM02yDRQEi=cL_rTH_ROLDYgOx004sQu7A@mail.gmail.com
---
src/backend/executor/execUtils.c | 22 +++
src/backend/executor/nodeModifyTable.c | 160 +++++++++++++------
src/include/nodes/execnodes.h | 3 +-
src/test/regress/expected/for_portion_of.out | 123 ++++++++++++++
src/test/regress/sql/for_portion_of.sql | 84 ++++++++++
5 files changed, 344 insertions(+), 48 deletions(-)
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index 1eb6b9f1f40..5df7f2edf85 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -1430,7 +1430,29 @@ ExecGetExtraUpdatedCols(ResultRelInfo *relinfo, EState *estate)
{
/* Compute the info if we didn't already */
if (!relinfo->ri_extraUpdatedCols_valid)
+ {
+ if (relinfo->ri_forPortionOf)
+ {
+ MemoryContext oldContext;
+
+ AttrNumber rangeAttno = relinfo->ri_forPortionOf->fp_rangeAttno;
+
+ oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ /*
+ * For UPDATE ... FOR PORTION OF, the range column is actually
+ * being modified (narrowed via intersection), but it is not
+ * included in updatedCols because the user does not need UPDATE
+ * permission on it. So we need to add it to ri_extraUpdatedCols
+ */
+ relinfo->ri_extraUpdatedCols =
+ bms_add_member(relinfo->ri_extraUpdatedCols, rangeAttno - FirstLowInvalidHeapAttributeNumber);
+
+ MemoryContextSwitchTo(oldContext);
+ }
+
ExecInitGenerated(relinfo, estate, CMD_UPDATE);
+ }
return relinfo->ri_extraUpdatedCols;
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index ef2a6bc6e9d..4395089565f 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -197,6 +197,8 @@ static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context,
static void ExecSetupTransitionCaptureState(ModifyTableState *mtstate, EState *estate);
static void fireBSTriggers(ModifyTableState *node);
static void fireASTriggers(ModifyTableState *node);
+static void ExecInitForPortionOf(ModifyTableState *mtstate, EState *estate,
+ ResultRelInfo *resultRelInfo);
/*
@@ -475,6 +477,21 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
else
updatedCols = NULL;
+ /*
+ * For UPDATE ... FOR PORTION OF, the range column is also being modified
+ * (narrowed via intersection), but it is not included in updatedCols
+ * because the user does not need UPDATE permission on it. We must
+ * account for it here so that generated columns referencing the range
+ * column are recomputed.
+ */
+ if (resultRelInfo->ri_forPortionOf)
+ {
+ AttrNumber rangeAttno = resultRelInfo->ri_forPortionOf->fp_rangeAttno;
+
+ updatedCols = bms_add_member(bms_copy(updatedCols),
+ rangeAttno - FirstLowInvalidHeapAttributeNumber);
+ }
+
/*
* Make sure these data structures are built in the per-query memory
* context so they'll survive throughout the query.
@@ -1408,7 +1425,6 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
ModifyTableState *mtstate = context->mtstate;
ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf;
- AttrNumber rangeAttno;
Datum oldRange;
TypeCacheEntry *typcache;
ForPortionOfState *fpoState;
@@ -1422,37 +1438,10 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
ReturnSetInfo rsi;
bool didInit = false;
bool shouldFree = false;
+ ResultRelInfo *rootRelInfo = mtstate->rootResultRelInfo;
LOCAL_FCINFO(fcinfo, 2);
- if (!resultRelInfo->ri_forPortionOf)
- {
- /*
- * If we don't have a ForPortionOfState yet, we must be a partition
- * child being hit for the first time. Make a copy from the root, with
- * our own tupleTableSlot. We do this lazily so that we don't pay the
- * price of unused partitions.
- */
- ForPortionOfState *leafState = makeNode(ForPortionOfState);
-
- if (!mtstate->rootResultRelInfo)
- elog(ERROR, "no root relation but ri_forPortionOf is uninitialized");
-
- fpoState = mtstate->rootResultRelInfo->ri_forPortionOf;
- Assert(fpoState);
-
- leafState->fp_rangeName = fpoState->fp_rangeName;
- leafState->fp_rangeType = fpoState->fp_rangeType;
- leafState->fp_rangeAttno = fpoState->fp_rangeAttno;
- leafState->fp_targetRange = fpoState->fp_targetRange;
- leafState->fp_Leftover = fpoState->fp_Leftover;
- /* Each partition needs a slot matching its tuple descriptor */
- leafState->fp_Existing =
- table_slot_create(resultRelInfo->ri_RelationDesc,
- &mtstate->ps.state->es_tupleTable);
-
- resultRelInfo->ri_forPortionOf = leafState;
- }
fpoState = resultRelInfo->ri_forPortionOf;
oldtupleSlot = fpoState->fp_Existing;
leftoverSlot = fpoState->fp_Leftover;
@@ -1473,21 +1462,13 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, tupleid, SnapshotAny, oldtupleSlot))
elog(ERROR, "failed to fetch tuple for FOR PORTION OF");
- /*
- * Get the old range of the record being updated/deleted. Must read with
- * the attno of the leaf partition being updated.
- */
-
- rangeAttno = forPortionOf->rangeVar->varattno;
- if (resultRelInfo->ri_RootResultRelInfo)
- map = ExecGetChildToRootMap(resultRelInfo);
- if (map != NULL)
- rangeAttno = map->attrMap->attnums[rangeAttno - 1];
slot_getallattrs(oldtupleSlot);
- if (oldtupleSlot->tts_isnull[rangeAttno - 1])
+ /* Get the old range of the record being updated/deleted. */
+
+ if (oldtupleSlot->tts_isnull[fpoState->fp_rangeAttno - 1])
elog(ERROR, "found a NULL range in a temporal table");
- oldRange = oldtupleSlot->tts_values[rangeAttno - 1];
+ oldRange = oldtupleSlot->tts_values[fpoState->fp_rangeAttno - 1];
/*
* Get the range's type cache entry. This is worth caching for the whole
@@ -1524,12 +1505,20 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
fcinfo->args[1].isnull = false;
/*
- * If there are partitions, we must insert into the root table, so we get
- * tuple routing. We already set up leftoverSlot with the root tuple
- * descriptor.
+ * For partitioned tables, we must read leftovers with the tuple descriptor
+ * of the child table, but insert into the root table to enable tuple
+ * routing. So leftoverSlot is configured with the root's tuple
+ * descriptor. However, for traditional table inheritance, we don't need
+ * tuple routing and just insert directly into the child table to preserve
+ * child-specific columns. In that case, leftoverSlot uses the child's
+ * (resultRelInfo) tuple descriptor.
*/
- if (resultRelInfo->ri_RootResultRelInfo)
+ if (rootRelInfo &&
+ rootRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ map = ExecGetChildToRootMap(resultRelInfo);
resultRelInfo = resultRelInfo->ri_RootResultRelInfo;
+ }
/*
* Insert a leftover for each value returned by the without_portion helper
@@ -1585,8 +1574,9 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
didInit = true;
}
- leftoverSlot->tts_values[forPortionOf->rangeVar->varattno - 1] = leftover;
- leftoverSlot->tts_isnull[forPortionOf->rangeVar->varattno - 1] = false;
+ leftoverSlot->tts_values[resultRelInfo->ri_forPortionOf->fp_rangeAttno - 1] = leftover;
+ leftoverSlot->tts_isnull[resultRelInfo->ri_forPortionOf->fp_rangeAttno - 1] = false;
+
ExecMaterializeSlot(leftoverSlot);
/*
@@ -4761,6 +4751,18 @@ ExecModifyTable(PlanState *pstate)
false, true);
}
+ /*
+ * If we don't have a ForPortionOfState yet, we must be a partition
+ * child being hit for the first time. Make a copy from the root, with
+ * our own tupleTableSlot. We do this lazily so that we don't pay the
+ * price of unused partitions.
+ */
+ if ((((ModifyTable *) context.mtstate->ps.plan)->forPortionOf) &&
+ !resultRelInfo->ri_forPortionOf)
+ {
+ ExecInitForPortionOf(context.mtstate, estate, resultRelInfo);
+ }
+
/*
* If resultRelInfo->ri_usesFdwDirectModify is true, all we need to do
* here is compute the RETURNING expressions.
@@ -5844,3 +5846,67 @@ ExecReScanModifyTable(ModifyTableState *node)
*/
elog(ERROR, "ExecReScanModifyTable is not implemented");
}
+
+/* ----------------------------------------------------------------
+ * ExecInitForPortionOf
+ *
+ * Initializes resultRelInfo->ri_forPortionOf for child tables.
+ * ----------------------------------------------------------------
+ */
+static void
+ExecInitForPortionOf(ModifyTableState *mtstate, EState *estate, ResultRelInfo *resultRelInfo)
+{
+ MemoryContext oldcxt;
+ ForPortionOfState *leafState;
+ ResultRelInfo *rootRelInfo = mtstate->rootResultRelInfo;
+ ForPortionOfState *fpoState;
+
+ if (!rootRelInfo)
+ elog(ERROR, "no root relation but ri_forPortionOf is uninitialized");
+
+ fpoState = mtstate->rootResultRelInfo->ri_forPortionOf;
+
+ /* Things built here have to last for the query duration. */
+ oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ leafState = makeNode(ForPortionOfState);
+
+ leafState->fp_rangeName = fpoState->fp_rangeName;
+ leafState->fp_rangeType = fpoState->fp_rangeType;
+ leafState->fp_targetRange = fpoState->fp_targetRange;
+
+ /*
+ * For partitioned tables we must read the leftovers using the child table's
+ * tuple descriptor, but then insert them into the root table (using its
+ * tuple descriptor) so we get tuple routing.
+ *
+ * For traditional table inheritance, we read and insert directly into this
+ * resultRelInfo; no tuple routing to the parent is required.
+ */
+ if (rootRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ TupleConversionMap *map = ExecGetChildToRootMap(resultRelInfo);
+ if (map)
+ leafState->fp_rangeAttno = map->attrMap->attnums[fpoState->fp_rangeAttno - 1];
+ else
+ leafState->fp_rangeAttno = fpoState->fp_rangeAttno;
+ leafState->fp_Leftover = fpoState->fp_Leftover;
+ }
+ else
+ {
+ leafState->fp_rangeAttno = fpoState->fp_rangeAttno;
+ leafState->fp_Leftover =
+ ExecInitExtraTupleSlot(mtstate->ps.state,
+ RelationGetDescr(resultRelInfo->ri_RelationDesc),
+ &TTSOpsVirtual);
+ }
+
+ /* Each partition needs a slot matching its tuple descriptor */
+ leafState->fp_Existing =
+ table_slot_create(resultRelInfo->ri_RelationDesc,
+ &mtstate->ps.state->es_tupleTable);
+
+ resultRelInfo->ri_forPortionOf = leafState;
+
+ MemoryContextSwitchTo(oldcxt);
+}
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 13359180d25..53c138310db 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -477,7 +477,8 @@ typedef struct ForPortionOfState
NodeTag type;
char *fp_rangeName; /* the column named in FOR PORTION OF */
- Oid fp_rangeType; /* the type of the FOR PORTION OF expression */
+ Oid fp_rangeType; /* the base type (not domain) of the FOR
+ * PORTION OF expression */
int fp_rangeAttno; /* the attno of the range column */
Datum fp_targetRange; /* the range/multirange from FOR PORTION OF */
TypeCacheEntry *fp_leftoverstypcache; /* type cache entry of the range */
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 31f772c723d..4405e88c9cc 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -1365,6 +1365,9 @@ $$;
CREATE TRIGGER fpo_before_stmt
BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_stmt1
+ BEFORE UPDATE OF valid_at ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
CREATE TRIGGER fpo_after_insert_stmt
AFTER INSERT ON for_portion_of_test
FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
@@ -1378,6 +1381,9 @@ CREATE TRIGGER fpo_after_delete_stmt
CREATE TRIGGER fpo_before_row
BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
CREATE TRIGGER fpo_after_insert_row
AFTER INSERT ON for_portion_of_test
FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
@@ -1394,9 +1400,15 @@ UPDATE for_portion_of_test
NOTICE: fpo_before_stmt: BEFORE UPDATE STATEMENT:
NOTICE: old: <NULL>
NOTICE: new: <NULL>
+NOTICE: fpo_before_stmt1: BEFORE UPDATE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
NOTICE: fpo_before_row: BEFORE UPDATE ROW:
NOTICE: old: [2019-01-01,2030-01-01)
NOTICE: new: [2021-01-01,2022-01-01)
+NOTICE: fpo_before_row1: BEFORE UPDATE ROW:
+NOTICE: old: [2019-01-01,2030-01-01)
+NOTICE: new: [2021-01-01,2022-01-01)
NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
NOTICE: old: <NULL>
NOTICE: new: <NULL>
@@ -2097,4 +2109,115 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
(4 rows)
DROP TABLE temporal_partitioned;
+-- UPDATE FOR PORTION OF with generated stored columns
+-- The generated column depends on the range column, so it must be
+-- recomputed when FOR PORTION OF narrows the range.
+CREATE TABLE fpo_generated (
+ id int,
+ valid_at int4range,
+ range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED,
+ range_lenv int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at))
+);
+INSERT INTO fpo_generated (id, valid_at) VALUES (1, '[10,100)');
+SELECT * FROM fpo_generated ORDER BY valid_at;
+ id | valid_at | range_len | range_lenv
+----+----------+-----------+------------
+ 1 | [10,100) | 90 | 90
+(1 row)
+
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON fpo_generated
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row2
+ BEFORE UPDATE OF valid_at ON fpo_generated
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+-- After the FOR PORTION OF (FPO) update, all three resulting rows
+-- (leftover-before, updated, and leftover-after) must contain the correct
+-- values for range_len and range_lenv.
+-- Triggers fpo_before_row1 and fpo_before_row2 should also be fired.
+UPDATE fpo_generated
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+NOTICE: fpo_before_row2: BEFORE UPDATE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row1: BEFORE UPDATE ROW:
+NOTICE: old: [10,100)
+NOTICE: new: [30,70)
+SELECT * FROM fpo_generated ORDER BY valid_at;
+ id | valid_at | range_len | range_lenv
+----+----------+-----------+------------
+ 1 | [10,30) | 20 | 20
+ 2 | [30,70) | 40 | 40
+ 1 | [70,100) | 30 | 30
+(3 rows)
+
+-- Also test with a generated column that references both a SET column
+-- and the range column.
+DROP TABLE fpo_generated;
+CREATE TABLE fpo_generated (
+ id int,
+ valid_at int4range,
+ id_plus_len int GENERATED ALWAYS AS (id + upper(valid_at) - lower(valid_at)) STORED,
+ id_plus_lenv int GENERATED ALWAYS AS (id + upper(valid_at) - lower(valid_at))
+);
+INSERT INTO fpo_generated (id, valid_at) VALUES (1, '[10,100)');
+SELECT * FROM fpo_generated ORDER BY valid_at;
+ id | valid_at | id_plus_len | id_plus_lenv
+----+----------+-------------+--------------
+ 1 | [10,100) | 91 | 91
+(1 row)
+
+UPDATE fpo_generated
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+SELECT * FROM fpo_generated ORDER BY valid_at;
+ id | valid_at | id_plus_len | id_plus_lenv
+----+----------+-------------+--------------
+ 1 | [10,30) | 21 | 21
+ 2 | [30,70) | 42 | 42
+ 1 | [70,100) | 31 | 31
+(3 rows)
+
+DROP TABLE fpo_generated;
+-- UPDATE FOR PORTION OF with table inheritance
+-- Leftover rows must stay in the child table, preserving child-specific columns.
+CREATE TABLE fpo_inh_parent (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+CREATE TABLE fpo_inh_child (
+ description text
+) INHERITS (fpo_inh_parent);
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+-- Update targets the parent; the matching row lives in the child.
+UPDATE fpo_inh_parent FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-10-01'
+ SET name = 'one^1';
+-- All three rows should be in the child, with description preserved.
+SELECT tableoid::regclass, * FROM fpo_inh_parent ORDER BY valid_at;
+ tableoid | id | valid_at | name
+---------------+-------+-------------------------+-------
+ fpo_inh_child | [1,2) | [2018-01-01,2018-04-01) | one
+ fpo_inh_child | [1,2) | [2018-04-01,2018-10-01) | one^1
+ fpo_inh_child | [1,2) | [2018-10-01,2019-01-01) | one
+(3 rows)
+
+SELECT * FROM fpo_inh_child ORDER BY valid_at;
+ id | valid_at | name | description
+-------+-------------------------+-------+-------------
+ [1,2) | [2018-01-01,2018-04-01) | one | initial
+ [1,2) | [2018-04-01,2018-10-01) | one^1 | initial
+ [1,2) | [2018-10-01,2019-01-01) | one | initial
+(3 rows)
+
+-- No rows should have leaked into the parent.
+SELECT * FROM ONLY fpo_inh_parent ORDER BY valid_at;
+ id | valid_at | name
+----+----------+------
+(0 rows)
+
+DROP TABLE fpo_inh_parent CASCADE;
+NOTICE: drop cascades to table fpo_inh_child
RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index d4062acf1d1..95efa640389 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -913,6 +913,10 @@ CREATE TRIGGER fpo_before_stmt
BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_stmt1
+ BEFORE UPDATE OF valid_at ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+
CREATE TRIGGER fpo_after_insert_stmt
AFTER INSERT ON for_portion_of_test
FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
@@ -931,6 +935,10 @@ CREATE TRIGGER fpo_before_row
BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
CREATE TRIGGER fpo_after_insert_row
AFTER INSERT ON for_portion_of_test
FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
@@ -1365,4 +1373,80 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
+-- UPDATE FOR PORTION OF with generated stored columns
+-- The generated column depends on the range column, so it must be
+-- recomputed when FOR PORTION OF narrows the range.
+CREATE TABLE fpo_generated (
+ id int,
+ valid_at int4range,
+ range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED,
+ range_lenv int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at))
+);
+INSERT INTO fpo_generated (id, valid_at) VALUES (1, '[10,100)');
+
+SELECT * FROM fpo_generated ORDER BY valid_at;
+
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON fpo_generated
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_before_row2
+ BEFORE UPDATE OF valid_at ON fpo_generated
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+
+-- After the FOR PORTION OF (FPO) update, all three resulting rows
+-- (leftover-before, updated, and leftover-after) must contain the correct
+-- values for range_len and range_lenv.
+-- Triggers fpo_before_row1 and fpo_before_row2 should also be fired.
+UPDATE fpo_generated
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+
+SELECT * FROM fpo_generated ORDER BY valid_at;
+
+-- Also test with a generated column that references both a SET column
+-- and the range column.
+DROP TABLE fpo_generated;
+CREATE TABLE fpo_generated (
+ id int,
+ valid_at int4range,
+ id_plus_len int GENERATED ALWAYS AS (id + upper(valid_at) - lower(valid_at)) STORED,
+ id_plus_lenv int GENERATED ALWAYS AS (id + upper(valid_at) - lower(valid_at))
+);
+
+INSERT INTO fpo_generated (id, valid_at) VALUES (1, '[10,100)');
+SELECT * FROM fpo_generated ORDER BY valid_at;
+
+UPDATE fpo_generated
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+SELECT * FROM fpo_generated ORDER BY valid_at;
+DROP TABLE fpo_generated;
+
+
+-- UPDATE FOR PORTION OF with table inheritance
+-- Leftover rows must stay in the child table, preserving child-specific columns.
+CREATE TABLE fpo_inh_parent (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+CREATE TABLE fpo_inh_child (
+ description text
+) INHERITS (fpo_inh_parent);
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+
+-- Update targets the parent; the matching row lives in the child.
+UPDATE fpo_inh_parent FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-10-01'
+ SET name = 'one^1';
+
+-- All three rows should be in the child, with description preserved.
+SELECT tableoid::regclass, * FROM fpo_inh_parent ORDER BY valid_at;
+SELECT * FROM fpo_inh_child ORDER BY valid_at;
+-- No rows should have leaked into the parent.
+SELECT * FROM ONLY fpo_inh_parent ORDER BY valid_at;
+
+DROP TABLE fpo_inh_parent CASCADE;
+
RESET datestyle;
--
2.45.0
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column
In-Reply-To: <CA+renyWD+XXifwswE74vhjooqbiVKu4qVhLvpMcUQBzrjVjT7A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox