public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: PostgreSQL-development <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: UPDATE FOR PORTION OF + table inheritance misroutes leftover rows to parent
Date: Tue, 7 Apr 2026 02:28:50 -0700
Message-ID: <CAHg+QDcsXsUVaZ+JwM02yDRQEi=cL_rTH_ROLDYgOx004sQu7A@mail.gmail.com> (raw)
Hi hackers,
I found a bug with UPDATE ... FOR PORTION OF when used with traditional
table inheritance. When an UPDATE targets a parent table and the matching
row lives in a child that has extra columns, the temporal leftover rows are
inserted into the parent table instead of back into the child. This causes
child-specific column values to be lost.
Reproduction:
SET datestyle TO ISO, YMD;
CREATE TABLE parent (
id int4range,
valid_at daterange,
name text
);
CREATE TABLE child (
description text
) INHERITS (parent);
INSERT INTO child (id, valid_at, name, description)
VALUES ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
UPDATE parent
FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-10-01'
SET name = 'one^1';
Expected: all three resulting rows stay in "child" with description
preserved:
child | [1,2) | [2018-01-01,2018-04-01) | one | initial
child | [1,2) | [2018-04-01,2018-10-01) | one^1 | initial
child | [1,2) | [2018-10-01,2019-01-01) | one | initial
Actual: the two leftover rows land in "parent", losing the description
column:
parent | [1,2) | [2018-01-01,2018-04-01) | one
child | [1,2) | [2018-04-01,2018-10-01) | one^1 | initial
parent | [1,2) | [2018-10-01,2019-01-01) | one
Root cause:
In ExecForPortionOfLeftovers(), the code unconditionally redirects leftover
inserts to ri_RootResultRelInfo whenever the current resultRelInfo has one
set:
if (resultRelInfo->ri_RootResultRelInfo)
resultRelInfo = resultRelInfo->ri_RootResultRelInfo;
The comment says “If there are partitions, we must insert into the root
table, so we get tuple routing.” That logic makes sense for partitioned
tables, because tuple routing will forward the INSERT to the correct
partition.
However, this breaks traditional inheritance. In that case there’s no tuple
routing, so the insert ends up going directly into the parent table. On top
of that, the fp_Leftover slot uses the root’s tuple descriptor, which
doesn’t include the child’s extra columns. Attached a draft patch to fix
this issue and also added tests.
Thanks,
Satya
Attachments:
[application/octet-stream] v1-0001-fpo-inheritance-fix.patch (5.2K, 3-v1-0001-fpo-inheritance-fix.patch)
download | inline diff:
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index dfd7b33a..30cf7698 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1520,12 +1520,25 @@ 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
+ * If the target is partitioned, we must insert into the root table so we
+ * get tuple routing. We already set up leftoverSlot with the root tuple
* descriptor.
+ *
+ * For traditional inheritance (no partition tuple routing), we must
+ * insert back into the same child table so that child-specific columns
+ * are preserved. In that case we need a leftover slot that matches the
+ * child's tuple descriptor rather than the root's.
*/
- if (resultRelInfo->ri_RootResultRelInfo)
+ if (resultRelInfo->ri_RootResultRelInfo &&
+ mtstate->mt_partition_tuple_routing)
resultRelInfo = resultRelInfo->ri_RootResultRelInfo;
+ else if (resultRelInfo->ri_RootResultRelInfo)
+ {
+ leftoverSlot =
+ ExecInitExtraTupleSlot(estate,
+ RelationGetDescr(resultRelInfo->ri_RelationDesc),
+ &TTSOpsVirtual);
+ }
/*
* Insert a leftover for each value returned by the without_portion helper
@@ -1557,8 +1570,12 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
* 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.
+ *
+ * For traditional inheritance (no partition routing), we keep the
+ * child's tuple format so that child-specific columns are
+ * preserved.
*/
- if (map != NULL)
+ if (map != NULL && mtstate->mt_partition_tuple_routing)
{
leftoverSlot = execute_attr_map_slot(map->attrMap,
oldtupleSlot,
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 31f772c7..250e3653 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2097,4 +2097,44 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
(4 rows)
DROP TABLE temporal_partitioned;
+-- UPDATE FOR PORTION OF with table inheritance
+-- Leftover rows must stay in the child table, preserving child-specific columns.
+CREATE TABLE fpo_inh_parent (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+CREATE TABLE fpo_inh_child (
+ description text
+) INHERITS (fpo_inh_parent);
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+-- Update targets the parent; the matching row lives in the child.
+UPDATE fpo_inh_parent FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-10-01'
+ SET name = 'one^1';
+-- All three rows should be in the child, with description preserved.
+SELECT tableoid::regclass, * FROM fpo_inh_parent ORDER BY valid_at;
+ tableoid | id | valid_at | name
+---------------+-------+-------------------------+-------
+ fpo_inh_child | [1,2) | [2018-01-01,2018-04-01) | one
+ fpo_inh_child | [1,2) | [2018-04-01,2018-10-01) | one^1
+ fpo_inh_child | [1,2) | [2018-10-01,2019-01-01) | one
+(3 rows)
+
+SELECT * FROM fpo_inh_child ORDER BY valid_at;
+ id | valid_at | name | description
+-------+-------------------------+-------+-------------
+ [1,2) | [2018-01-01,2018-04-01) | one | initial
+ [1,2) | [2018-04-01,2018-10-01) | one^1 | initial
+ [1,2) | [2018-10-01,2019-01-01) | one | initial
+(3 rows)
+
+-- No rows should have leaked into the parent.
+SELECT * FROM ONLY fpo_inh_parent ORDER BY valid_at;
+ id | valid_at | name
+----+----------+------
+(0 rows)
+
+DROP TABLE fpo_inh_parent CASCADE;
+NOTICE: drop cascades to table fpo_inh_child
RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index d4062acf..bb7c8046 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1365,4 +1365,30 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
+-- UPDATE FOR PORTION OF with table inheritance
+-- Leftover rows must stay in the child table, preserving child-specific columns.
+
+CREATE TABLE fpo_inh_parent (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+CREATE TABLE fpo_inh_child (
+ description text
+) INHERITS (fpo_inh_parent);
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+
+-- Update targets the parent; the matching row lives in the child.
+UPDATE fpo_inh_parent FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-10-01'
+ SET name = 'one^1';
+
+-- All three rows should be in the child, with description preserved.
+SELECT tableoid::regclass, * FROM fpo_inh_parent ORDER BY valid_at;
+SELECT * FROM fpo_inh_child ORDER BY valid_at;
+-- No rows should have leaked into the parent.
+SELECT * FROM ONLY fpo_inh_parent ORDER BY valid_at;
+
+DROP TABLE fpo_inh_parent CASCADE;
+
RESET datestyle;
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: UPDATE FOR PORTION OF + table inheritance misroutes leftover rows to parent
In-Reply-To: <CAHg+QDcsXsUVaZ+JwM02yDRQEi=cL_rTH_ROLDYgOx004sQu7A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox