Subject: Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables

From: Chao Li <[email-masked]>
Date: Thu, 7 May 2026 11:40:12 +0800

Hi,

While testing UPDATE FOR PORTION OF, I found a bug with inheritance tables. The following repro shows the problem more clearly than a description in words:
```
evantest=# create table p (id int, valid_at daterange, name text);
CREATE TABLE
evantest=# create table c (extra text) inherits (p);
CREATE TABLE
evantest=# insert into c values (1, daterange('2000-01-01', '2010-01-01'), 'old', 'x');
INSERT 0 1
evantest=# update p for portion of valid_at from '2001-01-01' to '2002-01-01' set name = 'new' where id = 1;
UPDATE 1
evantest=# select * from only p;
 id |        valid_at         | name
----+-------------------------+------
  1 | [2000-01-01,2001-01-01) | old
  1 | [2002-01-01,2010-01-01) | old
(2 rows)

evantest=# select * from only c;
 id |        valid_at         | name | extra
----+-------------------------+------+-------
  1 | [2001-01-01,2002-01-01) | new  | x
(1 row)
```

In this repro, the original tuple is inserted into the child table c, while the parent table p is empty. After the update, the updated portion is left in c, but the two leftover ranges are inserted into p, which is clearly wrong.

The same bug exists for DELETE FOR PORTION OF with inheritance tables as well:
```
evantest=# delete from p for portion of valid_at from '2001-01-01' to '2002-01-01' where id = 1;
DELETE 1
evantest=# select * from only p;
 id |        valid_at         | name
----+-------------------------+------
  1 | [2000-01-01,2001-01-01) | old
  1 | [2002-01-01,2010-01-01) | old
(2 rows)

evantest=# select * from only c;
 id | valid_at | name | extra
----+----------+------+-------
(0 rows)
```

After looking into the code, I found that leftover row insertion only considers the partitioned-table case, where leftovers need to be inserted through the root relation for partition routing. Plain inheritance is different, leftover rows should be inserted back into the actual child relation.

While debugging this, I also noticed another issue around mapping the range column’s attnum. In multiple-inheritance cases, the range column’s attnum in a child table may be different from the one in its parent, so we need to use the child’s actual attnum.

Please see the attached patch for the fix details and the new tests. Since I believe this bug was introduced in 19, I’m going to add it to the open items.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

---

From: Chao Li <[email-masked]>
Date: Sat, 9 May 2026 13:47:22 +0800

> On May 7, 2026, at 11:40, Chao Li <[email-masked]> wrote:
> 
> Hi,
> 
> <v1-0001-Fix-FOR-PORTION-OF-leftovers-for-inheritance-chil.patch>

Merged into [1].

[1] https://postgr.es/m/CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=[email-masked]

Best regards,

---

From: Paul A Jungwirth <[email-masked]>
Date: Mon, 25 May 2026 16:01:31 -0700

> [1] https://postgr.es/m/CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=[email-masked]

Here is a new patch for this. I don't think it makes sense to combine
it with the other fix anymore.

Per [1], we are now checking for UPDATE permission on the FOR PORTION
OF column, so most of that other patch went away, and the fix here can
be done independently. That's what v2 here does. I didn't really
change anything from the v12-0002 patch on that other thread, except
for moving some tests into the other patch (since they had nothing to
do with traditional inheritance). The patch here now passes, whether
applied to master directly or applied on top of v13 from the
updatedCols fix. (Combining them will give a rebase conflict in the
test files, but it's pretty trivial to fix.)

[1] https://www.postgresql.org/message-id/CA%2BrenyXyLfvtvVv--hGWGTgzFP%3D-%2BdPLy4RWvEmioAPyJMM%2Buw%40mail.gmail.com

Yours,

-- 
Paul              ~{:-)
[email-masked]
From 452ac46a3aebe1e84f3e40abedd63f64985eafa1 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email-masked]>
Date: Mon, 25 May 2026 15:44:33 -0700
Subject: [PATCH v2] 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.
---
 src/backend/executor/nodeModifyTable.c       | 151 +++++++++++++------
 src/include/nodes/execnodes.h                |   3 +-
 src/test/regress/expected/for_portion_of.out | 131 ++++++++++++++++
 src/test/regress/sql/for_portion_of.sql      |  73 +++++++++
 4 files changed, 310 insertions(+), 48 deletions(-)

diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 478cb01783c..8415a2a4d1b 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -199,6 +199,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);

 /*
@@ -1410,7 +1412,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;
@@ -1425,37 +1426,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;
@@ -1476,21 +1450,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
@@ -1528,12 +1494,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
@@ -1602,8 +1576,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);
 
 		/*
@@ -4778,6 +4752,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.
@@ -5861,3 +5847,74 @@ 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 0c0a205c44b..3645cae58fd 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2152,4 +2152,135 @@ 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';
+-- Both 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
 RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index fd79a9b78e7..8aa45bfd814 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1398,4 +1398,77 @@ 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';
+-- Both 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;
+
 RESET datestyle;
-- 
2.47.3

---

From: Chao Li <[email-masked]>
Date: Tue, 26 May 2026 14:55:01 +0800

> On May 26, 2026, at 07:01, Paul A Jungwirth <[email-masked]> wrote:
> 
> <v2-0001-Fix-FOR-PORTION-OF-with-partitions-and-inheritanc.patch>

Now v2 mixes two separate changes:

1) Adding ExecInitForPortionOf() to address the UPDATE OF trigger issue
2) Fixing the inheritance/leftover bug originally reported in this thread

I’d prefer not to combine these in a single patch. Could you please split out the refactoring that adds ExecInitForPortionOf() into a separate patch, with tests showing that the UPDATE OF trigger issue is fixed? Then I can rework my inheritance/leftover fix on top of that.

Best regards,

---

From: Paul A Jungwirth <[email-masked]>
Date: Tue, 26 May 2026 09:14:04 -0700

> I’d prefer not to combine these in a single patch. Could you please split out the refactoring that adds ExecInitForPortionOf() into a separate patch, with tests showing that the UPDATE OF trigger i…

Thanks for taking a look! I agree about keeping the fixes separate.
UPDATE OF triggers are fixed by the patch on the other thread (with
tests). Since we now include the application-time column in
updatedCols, there is nothing else to do there.

I thought the ExecInitForPortionOf refactoring was still nice to keep,
and it seemed to fit better here. (I should have updated the commit
message not to mention UPDATE OF though.)

But thinking about it this morning, I realized: the planner already
prunes partitions before we get here. Lazily initializing the structs
is trying too hard. Is there some scenario where that actually saves
work? If not, we could just set up all the structs in
ExecInitModifyTable. That's what I was doing in an older version of
the original patch. And then the refactor with ExecInitForPortionOf
seems unnecessary. I'll send a patch with those changes, unless you
have objections. (Probably two separate patches: one to remove lazy
initialization, another to actually fix traditional inheritance.) If
you disagree, please let me know.

Yours,

---

From: Paul A Jungwirth <[email-masked]>
Date: Tue, 26 May 2026 18:28:51 -0700

> message not to mention UPDATE OF though.)

I looked at the original v1 patch again. I thought the commit message
was excellent, so I pulled that into the latest patch, also some
comment changes and the partitionRouting boolean and some test
double-checks. I still like how ExecInitForPortionOf cuts down on the
branchiness and the variables used to track attnums, and also how it
gathers a lot of the child table setup in one place. The v1 seemed
hard to trace all the cases from top to bottom. For instance it
initialized map for all child tables, but then only used it for
partitions.

> work?

Never mind, of course you can filter rows (and whole tables) besides
just by partition pruning/constraint exclusion.

I'm not sure whom to list as author/co-author/reviewer for this patch,
but I took a stab at it. I think most of the current code is from jian
he's version, but all three of us have contributed a lot by this
point.

Yours,

-- 
Paul              ~{:-)
[email-masked]
From 092ead32365fcc02c001d1223fbae4ff94b2a4e8 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email-masked]>
Date: Mon, 25 May 2026 15:44:33 -0700
Subject: [PATCH v3] Fix FOR PORTION OF for inheritance children

ExecForPortionOfLeftovers() assumed that any result relation with
ri_RootResultRelInfo should reinsert temporal leftovers through the root
relation.  That is correct for partitioned tables, where tuple routing is
needed, but it is wrong for plain inheritance.

When UPDATE/DELETE FOR PORTION OF is run on an inheritance parent and a
child row is split, the leftover rows must be inserted back into the child
relation.  Reinserting through the parent can lose child-only columns and
place the leftover rows in the wrong relation.

Fix this by distinguishing partitioned-table routing from plain
inheritance.  For partitioned tables, keep using the root leftover slot and
insert through the root relation.  For plain inheritance children, use a
leftover slot matching the child relation and insert directly into the
child.  Also keep translating the application-time column attno for child
relations, so multiple-inheritance cases with different attribute numbers
are handled correctly.

Added an ExecInitForPortionOf function to set up the ForPortionOfState for each
child table, which keeps most of these decisions localized instead of spread
out through ExecForPortionOfLeftovers.  Incidentally clarified a comment about
the rangetype stored in ForPortionOfState.

Add regression tests for UPDATE and DELETE FOR PORTION OF on inheritance
children, including a multiple-inheritance case where the range column has
a different attnum in the parent and child.

Author: jian he <[email-masked]>
Co-authored-by: Chao Li <[email-masked]>
Reviewed-by: Paul A. Jungwirth <[email-masked]>
Discussion: https://www.postgresql.org/message-id/[email-masked]
---
 src/backend/executor/nodeModifyTable.c       | 165 +++++++++++++------
 src/include/nodes/execnodes.h                |   3 +-
 src/test/regress/expected/for_portion_of.out | 158 ++++++++++++++++++
 src/test/regress/sql/for_portion_of.sql      |  85 ++++++++++
 4 files changed, 360 insertions(+), 51 deletions(-)

index 478cb01783c..92dbda18ad9 100644

 /*
@@ -1410,7 +1412,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;
@@ -1425,37 +1426,13 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 	ReturnSetInfo rsi;
 	bool		didInit = false;
 	bool		shouldFree = false;
+	ResultRelInfo *rootRelInfo = mtstate->rootResultRelInfo;
+	bool		partitionRouting =
+		rootRelInfo &&
+		rootRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_PARTITIONED_TABLE;
 
 	LOCAL_FCINFO(fcinfo, 2);
 
@@ -1476,21 +1453,13 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 
-	/*

 	/*
 	 * Get the range's type cache entry. This is worth caching for the whole
@@ -1528,12 +1497,19 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 	fcinfo->args[1].isnull = false;
 
 	/*
+	 * descriptor.  But for traditional table inheritance, we don't need
+	if (partitionRouting)
 
 	/*
 	 * Insert a leftover for each value returned by the without_portion helper
@@ -1575,9 +1551,9 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 		{
 			/*
 			 * Make a copy of the pre-UPDATE row. Then we'll overwrite the
-			 * range column below. Convert oldtuple to the base table's format
-			 * if necessary. We need to insert temporal leftovers through the
-			 * root partition so they get routed correctly.
+			 * range column below. Only partitioned targets need conversion to
+			 * the root table's format, because they reinsert through the root
+			 * relation for tuple routing.
 			 */
 			if (map != NULL)
 			{
@@ -1602,8 +1578,8 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 			didInit = true;
 		}

 		/*
@@ -4778,6 +4754,18 @@ ExecModifyTable(PlanState *pstate)
 														 false, true);
 		}
 
+		/*
@@ -5861,3 +5849,80 @@ ExecReScanModifyTable(ModifyTableState *node)
+ *		Partitions share the root leftover slot, since they must insert via
+ *		the root relation to get tuple routing. Plain inheritance children
+ *		must keep their own leftover slot and insert back into the child,
+ *		or else child-only column values and physical placement would be
+ *		lost.
+	 * this resultRelInfo; no tuple routing via the parent is required.
 
index 0c0a205c44b..58eee01fcdb 100644
@@ -2152,4 +2152,162 @@ SELECT * FROM fpo_rule ORDER BY f1;
 (2 rows)
 
 DROP TABLE fpo_rule;
+-- attnum of the range column is different in temporal_parent and mi_child
+SELECT attnum, attname
+  FROM pg_attribute
+  WHERE attrelid = 'temporal_parent'::regclass
+    AND attnum > 0 AND NOT attisdropped
+  ORDER BY attnum;
+ attnum | attname  
+--------+----------
+      1 | id
+      2 | valid_at
+      3 | name
+SELECT attnum, attname
+  FROM pg_attribute
+  WHERE attrelid = 'mi_child'::regclass
+    AND attnum > 0 AND NOT attisdropped
+  ORDER BY attnum;
+ attnum | attname  
+--------+----------
+      1 | prefix
+      2 | note
+      3 | id
+      4 | valid_at
+      5 | name
+(5 rows)
index fd79a9b78e7..075c9051445 100644
@@ -1398,4 +1398,89 @@ SELECT * FROM fpo_rule ORDER BY f1;
 
 DROP TABLE fpo_rule;
 
+-- attnum of the range column is different in temporal_parent and mi_child
+SELECT attnum, attname
+  FROM pg_attribute
+  WHERE attrelid = 'temporal_parent'::regclass
+    AND attnum > 0 AND NOT attisdropped
+  ORDER BY attnum;
+SELECT attnum, attname
+  FROM pg_attribute
+  WHERE attrelid = 'mi_child'::regclass
+    AND attnum > 0 AND NOT attisdropped
+  ORDER BY attnum;

---

From: Chao Li <[email-masked]>
Date: Wed, 27 May 2026 15:55:38 +0800

> On May 27, 2026, at 09:28, Paul A Jungwirth <[email-masked]> wrote:
> 

Hi Paul,

I just tested v3 with my original repro, and it has resolved the bug I reported.
```
evantest=# create table p (id int, valid_at daterange, name text);
CREATE TABLE
evantest=# create table c (extra text) inherits (p);
CREATE TABLE
evantest=# insert into c values (1, daterange('2000-01-01', '2010-01-01'), 'old', 'x');
INSERT 0 1
evantest=# update p for portion of valid_at from '2001-01-01' to '2002-01-01' set name = 'new' where id = 1;
UPDATE 1
evantest=# select * from only p;
 id | valid_at | name
----+----------+------
(0 rows)
evantest=# select * from c;
 id |        valid_at         | name | extra
----+-------------------------+------+-------
  1 | [2001-01-01,2002-01-01) | new  | x
  1 | [2000-01-01,2001-01-01) | old  | x
  1 | [2002-01-01,2010-01-01) | old  | x
(3 rows)
```

I also noticed that the “UPDATE OF” related information has been removed from the commit message. I can still reproduce the UPDATE OF issue with v3, but I think that is expected, and that issue will be resolved by the other patch.

So v3 looks clean and good to me. Thanks for updating the patch and making the two patches decoupled.

Best regards,

---

From: Chao Li <[email-masked]>
Date: Sun, 7 Jun 2026 11:03:49 +0800

> On May 27, 2026, at 15:55, Chao Li <[email-masked]> wrote:
> 
> 
> 

Rebased as requested by the CF.

Best regards,

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 8 Jun 2026 19:33:04 +0200

> Rebased as requested by the CF.

committed