v12-0002-Fix-FOR-PORTION-OF-with-partitions-and-inheritan.patch
text/x-patch
Filename: v12-0002-Fix-FOR-PORTION-OF-with-partitions-and-inheritan.patch
Type: text/x-patch
Part: 1
From 91a4008e6c38fcaef49d3320abfdeda3d008f200 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
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=hM0g@mail.gmail.com
Discussion: https://postgr.es/m/CAHg+QDcsXsUVaZ+JwM02yDRQEi=cL_rTH_ROLDYgOx004sQu7A@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