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
