public inbox for [email protected]  

help / color / mirror / Atom feed

Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables
4+ messages / 2 participants
[nested] [flat]

* Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables
@ 2026-05-07 03:40  Chao Li <[email protected]>
  0 siblings, 1 reply; 4+ messages in thread

From: Chao Li @ 2026-05-07 03:40 UTC (permalink / raw)
  To: PostgreSQL Hackers <[email protected]>

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/






Attachments:

  [application/octet-stream] v1-0001-Fix-FOR-PORTION-OF-leftovers-for-inheritance-chil.patch (14.1K, 2-v1-0001-Fix-FOR-PORTION-OF-leftovers-for-inheritance-chil.patch)
  download | inline diff:
From 92abeba40fb1ef795a7f0eea198d7c144aef20fe Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Thu, 7 May 2026 11:23:10 +0800
Subject: [PATCH v1] Fix FOR PORTION OF leftovers 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.

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: Chao Li <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/
---
 src/backend/executor/nodeModifyTable.c       |  76 ++++++++----
 src/test/regress/expected/for_portion_of.out | 119 +++++++++++++++++++
 src/test/regress/sql/for_portion_of.sql      |  80 +++++++++++++
 3 files changed, 252 insertions(+), 23 deletions(-)

diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4cb057ca4f9..df3e3bb1b98 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1409,7 +1409,11 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 	ModifyTableState *mtstate = context->mtstate;
 	ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
 	ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf;
-	AttrNumber	rangeAttno;
+	AttrNumber	actualRangeAttno;	/* attno of range column in the current
+									 * result relation */
+	AttrNumber	targetRangeAttno;	/* attno of range column in the target
+									 * table of the query */
+	AttrNumber	leftoverRangeAttno;
 	Datum		oldRange;
 	TypeCacheEntry *typcache;
 	ForPortionOfState *fpoState;
@@ -1422,18 +1426,30 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 	FmgrInfo	flinfo;
 	PgStat_FunctionCallUsage fcusage;
 	ReturnSetInfo rsi;
+	ResultRelInfo *rootRri = resultRelInfo->ri_RootResultRelInfo;
 	bool		didInit = false;
 	bool		shouldFree = false;
+	bool		partitionRouting;
 
 	LOCAL_FCINFO(fcinfo, 2);
 
+	partitionRouting =
+		rootRri != NULL &&
+		rootRri->ri_RelationDesc->rd_rel->relkind == RELKIND_PARTITIONED_TABLE;
+	targetRangeAttno = forPortionOf->rangeVar->varattno;
+
 	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.
+		 * If we don't have a ForPortionOfState yet, we must be a child
+		 * relation being hit for the first time. Make a copy from the root,
+		 * with slots matching the child's tuple descriptor.
+		 *
+		 * 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.
 		 */
 		ForPortionOfState *leafState = makeNode(ForPortionOfState);
 
@@ -1447,8 +1463,15 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 		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 */
+		if (partitionRouting)
+			leafState->fp_Leftover = fpoState->fp_Leftover;
+		else
+			leafState->fp_Leftover =
+				ExecInitExtraTupleSlot(mtstate->ps.state,
+									   resultRelInfo->ri_RelationDesc->rd_att,
+									   &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);
@@ -1476,20 +1499,22 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 		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.
+	 * Get the old range of the record being updated/deleted. The query
+	 * expression uses the target relation's attno, but the tuple slot may
+	 * belong to a child relation.  If so, translate it to the corresponding
+	 * child attno.
 	 */
 
-	rangeAttno = forPortionOf->rangeVar->varattno;
-	if (resultRelInfo->ri_RootResultRelInfo)
+	actualRangeAttno = targetRangeAttno;
+	if (rootRri != NULL)
 		map = ExecGetChildToRootMap(resultRelInfo);
 	if (map != NULL)
-		rangeAttno = map->attrMap->attnums[rangeAttno - 1];
+		actualRangeAttno = map->attrMap->attnums[actualRangeAttno - 1];
 	slot_getallattrs(oldtupleSlot);
 
-	if (oldtupleSlot->tts_isnull[rangeAttno - 1])
+	if (oldtupleSlot->tts_isnull[actualRangeAttno - 1])
 		elog(ERROR, "found a NULL range in a temporal table");
-	oldRange = oldtupleSlot->tts_values[rangeAttno - 1];
+	oldRange = oldtupleSlot->tts_values[actualRangeAttno - 1];
 
 	/*
 	 * Get the range's type cache entry. This is worth caching for the whole
@@ -1529,10 +1554,15 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
 	/*
 	 * 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.
+	 * descriptor for that case.
 	 */
-	if (resultRelInfo->ri_RootResultRelInfo)
-		resultRelInfo = resultRelInfo->ri_RootResultRelInfo;
+	if (partitionRouting)
+	{
+		resultRelInfo = rootRri;
+		leftoverRangeAttno = targetRangeAttno;
+	}
+	else
+		leftoverRangeAttno = actualRangeAttno;
 
 	/*
 	 * Insert a leftover for each value returned by the without_portion helper
@@ -1574,11 +1604,11 @@ 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)
+			if (partitionRouting && map != NULL)
 			{
 				leftoverSlot = execute_attr_map_slot(map->attrMap,
 													 oldtupleSlot,
@@ -1601,8 +1631,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[leftoverRangeAttno - 1] = leftover;
+		leftoverSlot->tts_isnull[leftoverRangeAttno - 1] = false;
 		ExecMaterializeSlot(leftoverSlot);
 
 		/*
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 0c0a205c44b..7d7847c9ad7 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2097,6 +2097,125 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
 (4 rows)
 
 DROP TABLE temporal_partitioned;
+-- UPDATE/DELETE FOR PORTION OF with inheritance
+CREATE TABLE fpo_parent (
+  id int,
+  valid_at daterange,
+  name text
+);
+CREATE TABLE fpo_child (
+  extra text
+) INHERITS (fpo_parent);
+INSERT INTO fpo_child VALUES
+  (1, daterange('2000-01-01', '2010-01-01'), 'old', 'x');
+UPDATE fpo_parent FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01'
+  SET name = 'new'
+  WHERE id = 1;
+SELECT * FROM ONLY fpo_parent;
+ id | valid_at | name 
+----+----------+------
+(0 rows)
+
+SELECT * FROM ONLY fpo_child;
+ 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)
+
+TRUNCATE TABLE fpo_child, fpo_parent;
+INSERT INTO fpo_child VALUES
+  (1, daterange('2000-01-01', '2010-01-01'), 'old', 'x');
+DELETE FROM fpo_parent FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01'
+  WHERE id = 1;
+SELECT * FROM ONLY fpo_parent;
+ id | valid_at | name 
+----+----------+------
+(0 rows)
+
+SELECT * FROM ONLY fpo_child;
+ id |        valid_at         | name | extra 
+----+-------------------------+------+-------
+  1 | [2000-01-01,2001-01-01) | old  | x
+  1 | [2002-01-01,2010-01-01) | old  | x
+(2 rows)
+
+DROP TABLE fpo_child, fpo_parent;
+-- UPDATE/DELETE FOR PORTION OF with inheritance and differing attnos
+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);
+-- 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
+(3 rows)
+
+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)
+
+INSERT INTO mi_child 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 * FROM ONLY temporal_parent;
+ id | valid_at | name 
+----+----------+------
+(0 rows)
+
+SELECT * FROM ONLY mi_child;
+ prefix | note | id |        valid_at         | name 
+--------+------+----+-------------------------+------
+ pfx    | memo |  1 | [2001-01-01,2002-01-01) | new
+ pfx    | memo |  1 | [2000-01-01,2001-01-01) | old
+ pfx    | memo |  1 | [2002-01-01,2010-01-01) | old
+(3 rows)
+
+TRUNCATE TABLE mi_child, other_parent, temporal_parent;
+INSERT INTO mi_child 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 * FROM ONLY temporal_parent;
+ id | valid_at | name 
+----+----------+------
+(0 rows)
+
+SELECT * FROM ONLY mi_child;
+ 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)
+
+DROP TABLE mi_child, other_parent, temporal_parent;
 -- UPDATE/DELETE FOR PORTION OF with RULEs
 CREATE TABLE fpo_rule (f1 bigint, f2 int4range);
 INSERT INTO fpo_rule VALUES (1, '[1, 11)');
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index fd79a9b78e7..83a2bc9a82c 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1365,6 +1365,86 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
 
 DROP TABLE temporal_partitioned;
 
+-- UPDATE/DELETE FOR PORTION OF with inheritance
+CREATE TABLE fpo_parent (
+  id int,
+  valid_at daterange,
+  name text
+);
+CREATE TABLE fpo_child (
+  extra text
+) INHERITS (fpo_parent);
+
+INSERT INTO fpo_child VALUES
+  (1, daterange('2000-01-01', '2010-01-01'), 'old', 'x');
+
+UPDATE fpo_parent FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01'
+  SET name = 'new'
+  WHERE id = 1;
+
+SELECT * FROM ONLY fpo_parent;
+SELECT * FROM ONLY fpo_child;
+
+TRUNCATE TABLE fpo_child, fpo_parent;
+
+INSERT INTO fpo_child VALUES
+  (1, daterange('2000-01-01', '2010-01-01'), 'old', 'x');
+
+DELETE FROM fpo_parent FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01'
+  WHERE id = 1;
+
+SELECT * FROM ONLY fpo_parent;
+SELECT * FROM ONLY fpo_child;
+
+DROP TABLE fpo_child, fpo_parent;
+
+-- UPDATE/DELETE FOR PORTION OF with inheritance and differing attnos
+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);
+
+-- 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;
+
+INSERT INTO mi_child 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 * FROM ONLY temporal_parent;
+SELECT * FROM ONLY mi_child;
+
+TRUNCATE TABLE mi_child, other_parent, temporal_parent;
+
+INSERT INTO mi_child 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 * FROM ONLY temporal_parent;
+SELECT * FROM ONLY mi_child;
+
+DROP TABLE mi_child, other_parent, temporal_parent;
+
 -- UPDATE/DELETE FOR PORTION OF with RULEs
 CREATE TABLE fpo_rule (f1 bigint, f2 int4range);
 INSERT INTO fpo_rule VALUES (1, '[1, 11)');
-- 
2.50.1 (Apple Git-155)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables
@ 2026-05-09 05:47  Chao Li <[email protected]>
  parent: Chao Li <[email protected]>
  0 siblings, 1 reply; 4+ messages in thread

From: Chao Li @ 2026-05-09 05:47 UTC (permalink / raw)
  To: PostgreSQL Hackers <[email protected]>



> On May 7, 2026, at 11:40, Chao Li <[email protected]> wrote:
> 
> 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/
> 
> 
> 
> 
> <v1-0001-Fix-FOR-PORTION-OF-leftovers-for-inheritance-chil.patch>

Merged into [1].

[1] https://postgr.es/m/CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com

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










^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables
@ 2026-05-25 23:01  Paul A Jungwirth <[email protected]>
  parent: Chao Li <[email protected]>
  0 siblings, 1 reply; 4+ messages in thread

From: Paul A Jungwirth @ 2026-05-25 23:01 UTC (permalink / raw)
  To: Chao Li <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>

On Fri, May 8, 2026 at 10:48 PM Chao Li <[email protected]> wrote:
>
> > On May 7, 2026, at 11:40, Chao Li <[email protected]> wrote:
> > 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/
> >
> >
> >
> >
> > <v1-0001-Fix-FOR-PORTION-OF-leftovers-for-inheritance-chil.patch>
>
> Merged into [1].
>
> [1] https://postgr.es/m/CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com

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%40...

Yours,

-- 
Paul              ~{:-)
[email protected]


Attachments:

  [text/x-patch] v2-0001-Fix-FOR-PORTION-OF-with-partitions-and-inheritanc.patch (18.5K, 2-v2-0001-Fix-FOR-PORTION-OF-with-partitions-and-inheritanc.patch)
  download | inline diff:
From 452ac46a3aebe1e84f3e40abedd63f64985eafa1 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables
@ 2026-05-26 06:55  Chao Li <[email protected]>
  parent: Paul A Jungwirth <[email protected]>
  0 siblings, 0 replies; 4+ messages in thread

From: Chao Li @ 2026-05-26 06:55 UTC (permalink / raw)
  To: Paul A Jungwirth <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>



> On May 26, 2026, at 07:01, Paul A Jungwirth <[email protected]> wrote:
> 
> On Fri, May 8, 2026 at 10:48 PM Chao Li <[email protected]> wrote:
>> 
>>> On May 7, 2026, at 11:40, Chao Li <[email protected]> wrote:
>>> 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/
>>> 
>>> 
>>> 
>>> 
>>> <v1-0001-Fix-FOR-PORTION-OF-leftovers-for-inheritance-chil.patch>
>> 
>> Merged into [1].
>> 
>> [1] https://postgr.es/m/CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com
> 
> 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%40...
> 
> Yours,
> 
> -- 
> Paul              ~{:-)
> [email protected]
> <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,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/










^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2026-05-26 06:55 UTC | newest]

Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-07 03:40 Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables Chao Li <[email protected]>
2026-05-09 05:47 ` Chao Li <[email protected]>
2026-05-25 23:01   ` Paul A Jungwirth <[email protected]>
2026-05-26 06:55     ` Chao Li <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox