On Fri, May 8, 2026 at 12:10 AM Chao Li <[email protected]> wrote:
> > <v11-0001-Fix-FOR-PORTION-OF-column-dependency-tracking.patch><v11-0002-Fix-FOR-PORTION-OF-with-partitions-and-inheritan.patch>
>
> Thanks for updating the patch and making the separation. After reading v11, I
> still have a few comments for 0001.
>
> ```
> + if (relinfo->ri_forPortionOf)
> + {
> + AttrNumber rangeAttno =
> relinfo->ri_forPortionOf->fp_rangeAttno;
> +
> + if (!bms_is_member(rangeAttno -
> FirstLowInvalidHeapAttributeNumber,
> + updatedCols))
> + {
> + MemoryContext oldContext;
> +
> + oldContext =
> MemoryContextSwitchTo(estate->es_query_cxt);
> +
> + updatedCols = bms_copy(updatedCols);
> + updatedCols =
> + bms_add_member(updatedCols,
> + rangeAttno -
> FirstLowInvalidHeapAttributeNumber);
> +
> + MemoryContextSwitchTo(oldContext);
> + }
> }
> ```
>
> 1. I don’t think we should unconditionally do bms_copy, only if (updatedCols
> == perminfo->updatedCols), we need to make the copy.
You're saying we can skip the copy if execute_attr_map_cols already
made a new bms above. That's true. Since we're going to just use the
current memory context (see below), that seems safe.
> 2. I doubt if we need to switch to estate->es_query_cxt. Because
> ExecGetUpdatedCols() is called by ExecGetAllUpdatedCols(), and its header
> comment says the function runs in per-tuple memory context:
> ```
> /*
> * Return columns being updated, including generated columns
> *
> * The bitmap is allocated in per-tuple memory context. It's up to the caller
> to
> * copy it into a different context with the appropriate lifespan, if needed.
> */
> Bitmapset *
> ExecGetAllUpdatedCols(ResultRelInfo *relinfo, EState *estate)
> ```
>
> So I think bms_copy and bms_add_member should be just done in the current
> memory context.
Okay. I think using the current memory context is more correct anyway.
There are other callers, and using the query memory context isn't
necessarily what they want. Also the bms (potentially) allocated by
execute_attr_map_cols is in the current memory context, so doing
something different feels surprising. And it's safer not to change the
behavior. Maybe there is a memory leak because of a long-lived
context, but then it exists already. I added a comment to
ExecGetUpdatedCols to call out that we use the current memory context.
> 3. "rangeAttno - FirstLowInvalidHeapAttributeNumber” appears twice, maybe add
> a local variable to avoid the duplication.
Okay.
v12 attached.
Yours,
--
Paul ~{:-)
[email protected]
From 01cbcfc415f1d1331eaba1ef2fc0f78d9e63a0f0 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Thu, 7 May 2026 13:05:54 -0700
Subject: [PATCH v12 1/2] Fix FOR PORTION OF column dependency tracking
When FOR PORTION OF changes the application-time column, we need to mark the
column as updated, so that other GENERATED STORED columns recompute if they
depend on it, and similarly so that UPDATE OF triggers fire. We don't simply
record the column in updatedCols of RTEPermissionInfo, because the UPDATE/DELETE
should work even without permission to update that column.
Discussion: https://postgr.es/m/CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=h...@mail.gmail.com
Discussion: https://postgr.es/m/CAHg+QDcsXsUVaZ+JwM02yDRQEi=cl_rth_roldygox004sq...@mail.gmail.com
---
src/backend/executor/execUtils.c | 39 +++++++++-
src/test/regress/expected/for_portion_of.out | 80 ++++++++++++++++++++
src/test/regress/sql/for_portion_of.sql | 58 ++++++++++++++
3 files changed, 174 insertions(+), 3 deletions(-)
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index 1eb6b9f1f40..efafebafb5c 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -1403,25 +1403,58 @@ ExecGetInsertedCols(ResultRelInfo *relinfo, EState *estate)
return perminfo->insertedCols;
}
-/* Return a bitmap representing columns being updated */
+/*
+ * Return a bitmap representing columns being updated. If we allocate a new
+ * Bitmapset it will be in the current memory context.
+ */
Bitmapset *
ExecGetUpdatedCols(ResultRelInfo *relinfo, EState *estate)
{
RTEPermissionInfo *perminfo = GetResultRTEPermissionInfo(relinfo, estate);
+ Bitmapset *updatedCols;
if (perminfo == NULL)
return NULL;
+ updatedCols = perminfo->updatedCols;
+
/* Map the columns to child's attribute numbers if needed. */
if (relinfo->ri_RootResultRelInfo)
{
TupleConversionMap *map = ExecGetRootToChildMap(relinfo, estate);
if (map)
- return execute_attr_map_cols(map->attrMap, perminfo->updatedCols);
+ updatedCols = execute_attr_map_cols(map->attrMap, updatedCols);
+ }
+
+ /*
+ * For UPDATE ... FOR PORTION OF, the range column is being modified
+ * (narrowed via intersection), but it is not included in updatedCols
+ * because the user does not need UPDATE permission on it. Now manually
+ * add it to updatedCols.
+ *
+ * For partitioned tables, ri_forPortionOf->fp_rangeAttno is already
+ * mapped for the child partition, so we have to add it after the mapping
+ * just above. Also that makes it unsafe to mutate perminfo. We make an
+ * explicit copy of the Bitmapset since bms_add_member may change it
+ * in-place. XXX: Always add the unmapped attno instead (before mapping),
+ * and mutate perminfo, to avoid repeated allocations?
+ */
+ if (relinfo->ri_forPortionOf)
+ {
+ AttrNumber rangeAttno = relinfo->ri_forPortionOf->fp_rangeAttno - FirstLowInvalidHeapAttributeNumber;
+
+ if (!bms_is_member(rangeAttno, updatedCols))
+ {
+ /* Skip the copy if execute_attr_map_cols did it already. */
+ if (updatedCols == perminfo->updatedCols)
+ updatedCols = bms_copy(updatedCols);
+
+ updatedCols = bms_add_member(updatedCols, rangeAttno);
+ }
}
- return perminfo->updatedCols;
+ return updatedCols;
}
/* Return a bitmap representing generated columns being updated */
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 0c0a205c44b..094022d53ea 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2152,4 +2152,84 @@ SELECT * FROM fpo_rule ORDER BY f1;
(2 rows)
DROP TABLE fpo_rule;
+-- 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)
+
+-- 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.
+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 | 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;
+-- Test that UPDATE OF colname triggers fire if colname is valid_at:
+CREATE TABLE fpo_update_of_trigger (
+ id int,
+ valid_at int4range
+);
+INSERT INTO fpo_update_of_trigger (id, valid_at) VALUES (1, '[10,100)');
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON fpo_update_of_trigger
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row2
+ BEFORE UPDATE OF valid_at ON fpo_update_of_trigger
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+UPDATE fpo_update_of_trigger
+ 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)
+DROP TABLE fpo_update_of_trigger;
RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index fd79a9b78e7..ac5bce553eb 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1398,4 +1398,62 @@ SELECT * FROM fpo_rule ORDER BY f1;
DROP TABLE fpo_rule;
+-- 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;
+
+-- 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.
+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;
+
+-- Test that UPDATE OF colname triggers fire if colname is valid_at:
+CREATE TABLE fpo_update_of_trigger (
+ id int,
+ valid_at int4range
+);
+INSERT INTO fpo_update_of_trigger (id, valid_at) VALUES (1, '[10,100)');
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON fpo_update_of_trigger
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row2
+ BEFORE UPDATE OF valid_at ON fpo_update_of_trigger
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+UPDATE fpo_update_of_trigger
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+DROP TABLE fpo_update_of_trigger;
+
RESET datestyle;
--
2.47.3
From 91a4008e6c38fcaef49d3320abfdeda3d008f200 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Thu, 7 May 2026 15:31:12 -0700
Subject: [PATCH v12 2/2] Fix FOR PORTION OF with partitions and inheritance
- 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 updatedCols (used to recompute
GENERATED STORED columns and call UPDATE OF triggers, if the column has
changed).
- Clarified a comment about the rangetype stored in ForPortionOfState.
Discussion: https://postgr.es/m/CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=h...@mail.gmail.com
Discussion: https://postgr.es/m/CAHg+QDcsXsUVaZ+JwM02yDRQEi=cl_rth_roldygox004sq...@mail.gmail.com
---
src/backend/executor/nodeModifyTable.c | 150 ++++++++----
src/include/nodes/execnodes.h | 3 +-
src/test/regress/expected/for_portion_of.out | 241 +++++++++++++++----
src/test/regress/sql/for_portion_of.sql | 91 ++++++-
4 files changed, 388 insertions(+), 97 deletions(-)
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4cb057ca4f9..7b7f6b0fc10 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -198,6 +198,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);
/*
@@ -1409,7 +1411,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;
@@ -1424,37 +1425,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;
@@ -1475,21 +1449,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
@@ -1527,12 +1493,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
@@ -1601,8 +1575,8 @@ 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);
/*
@@ -4777,6 +4751,18 @@ ExecModifyTable(PlanState *pstate)
false, true);
}
+ /*
+ * If we don't have a ForPortionOfState yet, we must be a partition or
+ * inheritance 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.
@@ -5860,3 +5846,73 @@ 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;
+ TupleConversionMap *map;
+
+ 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;
+ map = ExecGetChildToRootMap(resultRelInfo);
+
+ /*
+ * fp_rangeAttno must match the tuple layout used for reading the old
+ * range value. The query uses the target relation's attno, so translate
+ * it to the child attno when the child has a different column layout.
+ */
+ if (map)
+ leafState->fp_rangeAttno = map->attrMap->attnums[fpoState->fp_rangeAttno - 1];
+ else
+ leafState->fp_rangeAttno = fpoState->fp_rangeAttno;
+
+ /*
+ * 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)
+ {
+ leafState->fp_Leftover = fpoState->fp_Leftover;
+ }
+ else
+ {
+ leafState->fp_Leftover =
+ ExecInitExtraTupleSlot(mtstate->ps.state,
+ RelationGetDescr(resultRelInfo->ri_RelationDesc),
+ &TTSOpsVirtual);
+ }
+
+ /* Each child relation 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 094022d53ea..b93375b8fea 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>
@@ -1986,6 +1998,7 @@ SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
DROP TABLE for_portion_of_test2;
DROP TYPE mydaterange;
-- Test FOR PORTION OF against a partitioned table.
+-- Include a GENERATED STORED column to test updatedCols column mapping.
-- temporal_partitioned_1 has the same attnums as the root
-- temporal_partitioned_3 has the different attnums from the root
-- temporal_partitioned_5 has the different attnums too, but reversed
@@ -1993,29 +2006,34 @@ CREATE TABLE temporal_partitioned (
id int4range,
valid_at daterange,
name text,
+ range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED,
CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
) PARTITION BY LIST (id);
CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)');
CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)');
CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)');
ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3;
-ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at CASCADE;
+NOTICE: drop cascades to column range_len of table temporal_partitioned_3
ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL;
+ALTER TABLE temporal_partitioned_3 ADD COLUMN range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED;
ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)');
ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5;
-ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at CASCADE;
+NOTICE: drop cascades to column range_len of table temporal_partitioned_5
ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL;
+ALTER TABLE temporal_partitioned_5 ADD COLUMN range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED;
ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)');
INSERT INTO temporal_partitioned (id, valid_at, name) VALUES
('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'),
('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'),
('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five');
SELECT * FROM temporal_partitioned;
- id | valid_at | name
--------+-------------------------+-------
- [1,2) | [2000-01-01,2010-01-01) | one
- [3,4) | [2000-01-01,2010-01-01) | three
- [5,6) | [2000-01-01,2010-01-01) | five
+ id | valid_at | name | range_len
+-------+-------------------------+-------+-----------
+ [1,2) | [2000-01-01,2010-01-01) | one | 3653
+ [3,4) | [2000-01-01,2010-01-01) | three | 3653
+ [5,6) | [2000-01-01,2010-01-01) | five | 3653
(3 rows)
-- Update without moving within partition 1
@@ -2046,54 +2064,54 @@ UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-0
id = '[3,4)'
WHERE id = '[5,6)';
-- Update all partitions at once (each with leftovers)
-SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+---------
- [1,2) | [2000-01-01,2000-03-01) | one
- [1,2) | [2000-03-01,2000-04-01) | one^1
- [1,2) | [2000-04-01,2000-06-01) | one
- [1,2) | [2000-07-01,2010-01-01) | one
- [2,3) | [2000-06-01,2000-07-01) | three^2
- [3,4) | [2000-01-01,2000-03-01) | three
- [3,4) | [2000-03-01,2000-04-01) | three^1
- [3,4) | [2000-04-01,2000-06-01) | three
- [3,4) | [2000-06-01,2000-07-01) | five^2
- [3,4) | [2000-07-01,2010-01-01) | three
- [4,5) | [2000-06-01,2000-07-01) | one^2
- [5,6) | [2000-01-01,2000-03-01) | five
- [5,6) | [2000-03-01,2000-04-01) | five^1
- [5,6) | [2000-04-01,2000-06-01) | five
- [5,6) | [2000-07-01,2010-01-01) | five
+SELECT *, upper(valid_at) - lower(valid_at) FROM temporal_partitioned ORDER BY id, valid_at;
+ id | valid_at | name | range_len | ?column?
+-------+-------------------------+---------+-----------+----------
+ [1,2) | [2000-01-01,2000-03-01) | one | 60 | 60
+ [1,2) | [2000-03-01,2000-04-01) | one^1 | 31 | 31
+ [1,2) | [2000-04-01,2000-06-01) | one | 61 | 61
+ [1,2) | [2000-07-01,2010-01-01) | one | 3471 | 3471
+ [2,3) | [2000-06-01,2000-07-01) | three^2 | 30 | 30
+ [3,4) | [2000-01-01,2000-03-01) | three | 60 | 60
+ [3,4) | [2000-03-01,2000-04-01) | three^1 | 31 | 31
+ [3,4) | [2000-04-01,2000-06-01) | three | 61 | 61
+ [3,4) | [2000-06-01,2000-07-01) | five^2 | 30 | 30
+ [3,4) | [2000-07-01,2010-01-01) | three | 3471 | 3471
+ [4,5) | [2000-06-01,2000-07-01) | one^2 | 30 | 30
+ [5,6) | [2000-01-01,2000-03-01) | five | 60 | 60
+ [5,6) | [2000-03-01,2000-04-01) | five^1 | 31 | 31
+ [5,6) | [2000-04-01,2000-06-01) | five | 61 | 61
+ [5,6) | [2000-07-01,2010-01-01) | five | 3471 | 3471
(15 rows)
SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+---------
- [1,2) | [2000-01-01,2000-03-01) | one
- [1,2) | [2000-03-01,2000-04-01) | one^1
- [1,2) | [2000-04-01,2000-06-01) | one
- [1,2) | [2000-07-01,2010-01-01) | one
- [2,3) | [2000-06-01,2000-07-01) | three^2
+ id | valid_at | name | range_len
+-------+-------------------------+---------+-----------
+ [1,2) | [2000-01-01,2000-03-01) | one | 60
+ [1,2) | [2000-03-01,2000-04-01) | one^1 | 31
+ [1,2) | [2000-04-01,2000-06-01) | one | 61
+ [1,2) | [2000-07-01,2010-01-01) | one | 3471
+ [2,3) | [2000-06-01,2000-07-01) | three^2 | 30
(5 rows)
SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at;
- name | id | valid_at
----------+-------+-------------------------
- three | [3,4) | [2000-01-01,2000-03-01)
- three^1 | [3,4) | [2000-03-01,2000-04-01)
- three | [3,4) | [2000-04-01,2000-06-01)
- five^2 | [3,4) | [2000-06-01,2000-07-01)
- three | [3,4) | [2000-07-01,2010-01-01)
- one^2 | [4,5) | [2000-06-01,2000-07-01)
+ name | id | valid_at | range_len
+---------+-------+-------------------------+-----------
+ three | [3,4) | [2000-01-01,2000-03-01) | 60
+ three^1 | [3,4) | [2000-03-01,2000-04-01) | 31
+ three | [3,4) | [2000-04-01,2000-06-01) | 61
+ five^2 | [3,4) | [2000-06-01,2000-07-01) | 30
+ three | [3,4) | [2000-07-01,2010-01-01) | 3471
+ one^2 | [4,5) | [2000-06-01,2000-07-01) | 30
(6 rows)
SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
- name | valid_at | id
---------+-------------------------+-------
- five | [2000-01-01,2000-03-01) | [5,6)
- five^1 | [2000-03-01,2000-04-01) | [5,6)
- five | [2000-04-01,2000-06-01) | [5,6)
- five | [2000-07-01,2010-01-01) | [5,6)
+ name | valid_at | id | range_len
+--------+-------------------------+-------+-----------
+ five | [2000-01-01,2000-03-01) | [5,6) | 60
+ five^1 | [2000-03-01,2000-04-01) | [5,6) | 31
+ five | [2000-04-01,2000-06-01) | [5,6) | 61
+ five | [2000-07-01,2010-01-01) | [5,6) | 3471
(4 rows)
DROP TABLE temporal_partitioned;
@@ -2152,6 +2170,137 @@ SELECT * FROM fpo_rule ORDER BY f1;
(2 rows)
DROP TABLE fpo_rule;
+-- UPDATE/DELETE 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);
+-- Update targets the parent; the matching row lives in the child.
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+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)
+
+-- Same test for DELETE instead of UPDATE:
+TRUNCATE fpo_inh_child, fpo_inh_parent;
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+DELETE FROM fpo_inh_parent FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-10-01';
+-- 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-10-01,2019-01-01) | one
+(2 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-10-01,2019-01-01) | one | initial
+(2 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
+-- UPDATE FOR PORTION OF with multiple inheritance
+-- Leftover rows must stay in the child table, even if the range column's
+-- attnum differs between the target parent and child.
+CREATE TABLE temporal_parent (
+ id int,
+ valid_at daterange,
+ name text
+);
+CREATE TABLE other_parent (
+ prefix text,
+ note text
+);
+CREATE TABLE mi_child () INHERITS (other_parent, temporal_parent);
+INSERT INTO mi_child (prefix, note, id, valid_at, name) VALUES
+ ('pfx', 'memo', 1, daterange('2000-01-01', '2010-01-01'), 'old');
+UPDATE temporal_parent FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01'
+ SET name = 'new'
+ WHERE id = 1;
+SELECT tableoid::regclass, * FROM temporal_parent ORDER BY valid_at;
+ tableoid | id | valid_at | name
+----------+----+-------------------------+------
+ mi_child | 1 | [2000-01-01,2001-01-01) | old
+ mi_child | 1 | [2001-01-01,2002-01-01) | new
+ mi_child | 1 | [2002-01-01,2010-01-01) | old
+(3 rows)
+
+SELECT * FROM mi_child ORDER BY valid_at;
+ prefix | note | id | valid_at | name
+--------+------+----+-------------------------+------
+ pfx | memo | 1 | [2000-01-01,2001-01-01) | old
+ pfx | memo | 1 | [2001-01-01,2002-01-01) | new
+ pfx | memo | 1 | [2002-01-01,2010-01-01) | old
+(3 rows)
+
+SELECT * FROM ONLY temporal_parent ORDER BY valid_at;
+ id | valid_at | name
+----+----------+------
+(0 rows)
+
+TRUNCATE mi_child, other_parent, temporal_parent;
+INSERT INTO mi_child (prefix, note, id, valid_at, name) VALUES
+ ('pfx', 'memo', 1, daterange('2000-01-01', '2010-01-01'), 'old');
+DELETE FROM temporal_parent FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01'
+ WHERE id = 1;
+SELECT tableoid::regclass, * FROM temporal_parent ORDER BY valid_at;
+ tableoid | id | valid_at | name
+----------+----+-------------------------+------
+ mi_child | 1 | [2000-01-01,2001-01-01) | old
+ mi_child | 1 | [2002-01-01,2010-01-01) | old
+(2 rows)
+
+SELECT * FROM mi_child ORDER BY valid_at;
+ prefix | note | id | valid_at | name
+--------+------+----+-------------------------+------
+ pfx | memo | 1 | [2000-01-01,2001-01-01) | old
+ pfx | memo | 1 | [2002-01-01,2010-01-01) | old
+(2 rows)
+
+SELECT * FROM ONLY temporal_parent ORDER BY valid_at;
+ id | valid_at | name
+----+----------+------
+(0 rows)
+
+DROP TABLE temporal_parent CASCADE;
+NOTICE: drop cascades to table mi_child
-- 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.
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index ac5bce553eb..316c3f73083 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);
@@ -1292,6 +1300,7 @@ DROP TABLE for_portion_of_test2;
DROP TYPE mydaterange;
-- Test FOR PORTION OF against a partitioned table.
+-- Include a GENERATED STORED column to test updatedCols column mapping.
-- temporal_partitioned_1 has the same attnums as the root
-- temporal_partitioned_3 has the different attnums from the root
-- temporal_partitioned_5 has the different attnums too, but reversed
@@ -1300,6 +1309,7 @@ CREATE TABLE temporal_partitioned (
id int4range,
valid_at daterange,
name text,
+ range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED,
CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
) PARTITION BY LIST (id);
CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)');
@@ -1307,13 +1317,15 @@ CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES
CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)');
ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3;
-ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at CASCADE;
ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL;
+ALTER TABLE temporal_partitioned_3 ADD COLUMN range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED;
ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)');
ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5;
-ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at CASCADE;
ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL;
+ALTER TABLE temporal_partitioned_5 ADD COLUMN range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED;
ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)');
INSERT INTO temporal_partitioned (id, valid_at, name) VALUES
@@ -1358,7 +1370,7 @@ UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-0
-- Update all partitions at once (each with leftovers)
-SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+SELECT *, upper(valid_at) - lower(valid_at) FROM temporal_partitioned ORDER BY id, valid_at;
SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at;
SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at;
SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
@@ -1398,6 +1410,79 @@ SELECT * FROM fpo_rule ORDER BY f1;
DROP TABLE fpo_rule;
+-- UPDATE/DELETE 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);
+
+-- Update targets the parent; the matching row lives in the child.
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+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;
+
+-- Same test for DELETE instead of UPDATE:
+TRUNCATE fpo_inh_child, fpo_inh_parent;
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+DELETE FROM fpo_inh_parent FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-10-01';
+-- 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;
+
+-- UPDATE FOR PORTION OF with multiple inheritance
+-- Leftover rows must stay in the child table, even if the range column's
+-- attnum differs between the target parent and child.
+CREATE TABLE temporal_parent (
+ id int,
+ valid_at daterange,
+ name text
+);
+CREATE TABLE other_parent (
+ prefix text,
+ note text
+);
+CREATE TABLE mi_child () INHERITS (other_parent, temporal_parent);
+
+INSERT INTO mi_child (prefix, note, id, valid_at, name) VALUES
+ ('pfx', 'memo', 1, daterange('2000-01-01', '2010-01-01'), 'old');
+
+UPDATE temporal_parent FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01'
+ SET name = 'new'
+ WHERE id = 1;
+
+SELECT tableoid::regclass, * FROM temporal_parent ORDER BY valid_at;
+SELECT * FROM mi_child ORDER BY valid_at;
+SELECT * FROM ONLY temporal_parent ORDER BY valid_at;
+
+TRUNCATE mi_child, other_parent, temporal_parent;
+INSERT INTO mi_child (prefix, note, id, valid_at, name) VALUES
+ ('pfx', 'memo', 1, daterange('2000-01-01', '2010-01-01'), 'old');
+
+DELETE FROM temporal_parent FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01'
+ WHERE id = 1;
+
+SELECT tableoid::regclass, * FROM temporal_parent ORDER BY valid_at;
+SELECT * FROM mi_child ORDER BY valid_at;
+SELECT * FROM ONLY temporal_parent ORDER BY valid_at;
+
+DROP TABLE temporal_parent CASCADE;
+
-- 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.
--
2.47.3
