MIME-Version: 1.0
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Tue, 7 Apr 2026 02:28:50 -0700
Message-ID: 
 <CAHg+QDcsXsUVaZ+JwM02yDRQEi=cL_rTH_ROLDYgOx004sQu7A@mail.gmail.com>
Subject: UPDATE FOR PORTION OF + table inheritance misroutes leftover rows to
 parent
To: PostgreSQL-development <pgsql-hackers@postgresql.org>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000ff19ac064edb69da"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDcsXsUVaZ%2BJwM02yDRQEi%3DcL_rTH_ROLDYgOx004sQu7A%40mail.gmail.com>
Precedence: bulk

--000000000000ff19ac064edb69da
Content-Type: multipart/alternative; boundary="000000000000ff19ab064edb69d8"

--000000000000ff19ab064edb69d8
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 =3D '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 =3D resultRelInfo->ri_RootResultRelInfo;


The comment says =E2=80=9CIf there are partitions, we must insert into the =
root
table, so we get tuple routing.=E2=80=9D That logic makes sense for partiti=
oned
tables, because tuple routing will forward the INSERT to the correct
partition.

However, this breaks traditional inheritance. In that case there=E2=80=99s =
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=E2=80=99s tuple descriptor, whi=
ch
doesn=E2=80=99t include the child=E2=80=99s extra columns. Attached a draft=
 patch to fix
this issue and also added tests.

Thanks,
Satya

--000000000000ff19ab064edb69d8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi hackers,<br><br>I found a bug with UPD=
ATE ... FOR PORTION OF when used with traditional table inheritance.=C2=A0 =
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 p=
arent table instead of back into the child.=C2=A0 This causes child-specifi=
c column values to be lost.<br><br>Reproduction:<br><br>=C2=A0 SET datestyl=
e TO ISO, YMD;<br><br>=C2=A0 CREATE TABLE parent (<br>=C2=A0 =C2=A0 id int4=
range,<br>=C2=A0 =C2=A0 valid_at daterange,<br>=C2=A0 =C2=A0 name text<br>=
=C2=A0 );<br>=C2=A0 CREATE TABLE child (<br>=C2=A0 =C2=A0 description text<=
br>=C2=A0 ) INHERITS (parent);<br><br>=C2=A0 INSERT INTO child (id, valid_a=
t, name, description)<br>=C2=A0 =C2=A0 VALUES (&#39;[1,2)&#39;, &#39;[2018-=
01-01,2019-01-01)&#39;, &#39;one&#39;, &#39;initial&#39;);<br><br>=C2=A0 UP=
DATE parent<br>=C2=A0 =C2=A0 FOR PORTION OF valid_at FROM &#39;2018-04-01&#=
39; TO &#39;2018-10-01&#39;<br>=C2=A0 =C2=A0 SET name =3D &#39;one^1&#39;;<=
br><br>Expected: all three resulting rows stay in &quot;child&quot; with de=
scription preserved:<br><br>=C2=A0 child | [1,2) | [2018-01-01,2018-04-01) =
| one =C2=A0 | initial<br>=C2=A0 child | [1,2) | [2018-04-01,2018-10-01) | =
one^1 | initial<br>=C2=A0 child | [1,2) | [2018-10-01,2019-01-01) | one =C2=
=A0 | initial<br><br>Actual: the two leftover rows land in &quot;parent&quo=
t;, losing the description column:<br><br>=C2=A0 parent | [1,2) | [2018-01-=
01,2018-04-01) | one<br>=C2=A0 child =C2=A0| [1,2) | [2018-04-01,2018-10-01=
) | one^1 | initial<br>=C2=A0 parent | [1,2) | [2018-10-01,2019-01-01) | on=
e<div><br><br>Root cause:<br><br>In ExecForPortionOfLeftovers(), the code u=
nconditionally redirects leftover inserts to ri_RootResultRelInfo whenever =
the current resultRelInfo has one set:<br><br>=C2=A0 =C2=A0 if (resultRelIn=
fo-&gt;ri_RootResultRelInfo)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 resultRelInfo =
=3D resultRelInfo-&gt;ri_RootResultRelInfo;<br><br><br>The comment says =E2=
=80=9CIf there are partitions, we must insert into the root table, so we ge=
t tuple routing.=E2=80=9D That logic makes sense for partitioned tables, be=
cause tuple routing will forward the INSERT to the correct partition.<br><b=
r>However, this breaks traditional inheritance. In that case there=E2=80=99=
s no tuple routing, so the insert ends up going directly into the parent ta=
ble. On top of that, the fp_Leftover slot uses the root=E2=80=99s tuple des=
criptor, which doesn=E2=80=99t include the child=E2=80=99s extra columns. A=
ttached a draft patch to fix this issue and also added tests.</div><div><br=
></div><div>Thanks,</div><div>Satya</div><div><br></div><div><br></div></di=
v>
</div>

--000000000000ff19ab064edb69d8--

--000000000000ff19ac064edb69da
Content-Type: application/octet-stream;
	name="v1-0001-fpo-inheritance-fix.patch"
Content-Disposition: attachment;
	filename="v1-0001-fpo-inheritance-fix.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mnof1i2t0>
X-Attachment-Id: f_mnof1i2t0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVNb2RpZnlUYWJsZS5jIGIvc3Jj
L2JhY2tlbmQvZXhlY3V0b3Ivbm9kZU1vZGlmeVRhYmxlLmMKaW5kZXggZGZkN2IzM2EuLjMwY2Y3
Njk4IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlTW9kaWZ5VGFibGUuYwor
KysgYi9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlTW9kaWZ5VGFibGUuYwpAQCAtMTUyMCwxMiAr
MTUyMCwyNSBAQCBFeGVjRm9yUG9ydGlvbk9mTGVmdG92ZXJzKE1vZGlmeVRhYmxlQ29udGV4dCAq
Y29udGV4dCwKIAlmY2luZm8tPmFyZ3NbMV0uaXNudWxsID0gZmFsc2U7CiAKIAkvKgotCSAqIElm
IHRoZXJlIGFyZSBwYXJ0aXRpb25zLCB3ZSBtdXN0IGluc2VydCBpbnRvIHRoZSByb290IHRhYmxl
LCBzbyB3ZSBnZXQKLQkgKiB0dXBsZSByb3V0aW5nLiBXZSBhbHJlYWR5IHNldCB1cCBsZWZ0b3Zl
clNsb3Qgd2l0aCB0aGUgcm9vdCB0dXBsZQorCSAqIElmIHRoZSB0YXJnZXQgaXMgcGFydGl0aW9u
ZWQsIHdlIG11c3QgaW5zZXJ0IGludG8gdGhlIHJvb3QgdGFibGUgc28gd2UKKwkgKiBnZXQgdHVw
bGUgcm91dGluZy4gIFdlIGFscmVhZHkgc2V0IHVwIGxlZnRvdmVyU2xvdCB3aXRoIHRoZSByb290
IHR1cGxlCiAJICogZGVzY3JpcHRvci4KKwkgKgorCSAqIEZvciB0cmFkaXRpb25hbCBpbmhlcml0
YW5jZSAobm8gcGFydGl0aW9uIHR1cGxlIHJvdXRpbmcpLCB3ZSBtdXN0CisJICogaW5zZXJ0IGJh
Y2sgaW50byB0aGUgc2FtZSBjaGlsZCB0YWJsZSBzbyB0aGF0IGNoaWxkLXNwZWNpZmljIGNvbHVt
bnMKKwkgKiBhcmUgcHJlc2VydmVkLiAgSW4gdGhhdCBjYXNlIHdlIG5lZWQgYSBsZWZ0b3ZlciBz
bG90IHRoYXQgbWF0Y2hlcyB0aGUKKwkgKiBjaGlsZCdzIHR1cGxlIGRlc2NyaXB0b3IgcmF0aGVy
IHRoYW4gdGhlIHJvb3Qncy4KIAkgKi8KLQlpZiAocmVzdWx0UmVsSW5mby0+cmlfUm9vdFJlc3Vs
dFJlbEluZm8pCisJaWYgKHJlc3VsdFJlbEluZm8tPnJpX1Jvb3RSZXN1bHRSZWxJbmZvICYmCisJ
CW10c3RhdGUtPm10X3BhcnRpdGlvbl90dXBsZV9yb3V0aW5nKQogCQlyZXN1bHRSZWxJbmZvID0g
cmVzdWx0UmVsSW5mby0+cmlfUm9vdFJlc3VsdFJlbEluZm87CisJZWxzZSBpZiAocmVzdWx0UmVs
SW5mby0+cmlfUm9vdFJlc3VsdFJlbEluZm8pCisJeworCQlsZWZ0b3ZlclNsb3QgPQorCQkJRXhl
Y0luaXRFeHRyYVR1cGxlU2xvdChlc3RhdGUsCisJCQkJCQkJCSAgIFJlbGF0aW9uR2V0RGVzY3Io
cmVzdWx0UmVsSW5mby0+cmlfUmVsYXRpb25EZXNjKSwKKwkJCQkJCQkJICAgJlRUU09wc1ZpcnR1
YWwpOworCX0KIAogCS8qCiAJICogSW5zZXJ0IGEgbGVmdG92ZXIgZm9yIGVhY2ggdmFsdWUgcmV0
dXJuZWQgYnkgdGhlIHdpdGhvdXRfcG9ydGlvbiBoZWxwZXIKQEAgLTE1NTcsOCArMTU3MCwxMiBA
QCBFeGVjRm9yUG9ydGlvbk9mTGVmdG92ZXJzKE1vZGlmeVRhYmxlQ29udGV4dCAqY29udGV4dCwK
IAkJCSAqIHJhbmdlIGNvbHVtbiBiZWxvdy4gQ29udmVydCBvbGR0dXBsZSB0byB0aGUgYmFzZSB0
YWJsZSdzIGZvcm1hdAogCQkJICogaWYgbmVjZXNzYXJ5LiBXZSBuZWVkIHRvIGluc2VydCB0ZW1w
b3JhbCBsZWZ0b3ZlcnMgdGhyb3VnaCB0aGUKIAkJCSAqIHJvb3QgcGFydGl0aW9uIHNvIHRoZXkg
Z2V0IHJvdXRlZCBjb3JyZWN0bHkuCisJCQkgKgorCQkJICogRm9yIHRyYWRpdGlvbmFsIGluaGVy
aXRhbmNlIChubyBwYXJ0aXRpb24gcm91dGluZyksIHdlIGtlZXAgdGhlCisJCQkgKiBjaGlsZCdz
IHR1cGxlIGZvcm1hdCBzbyB0aGF0IGNoaWxkLXNwZWNpZmljIGNvbHVtbnMgYXJlCisJCQkgKiBw
cmVzZXJ2ZWQuCiAJCQkgKi8KLQkJCWlmIChtYXAgIT0gTlVMTCkKKwkJCWlmIChtYXAgIT0gTlVM
TCAmJiBtdHN0YXRlLT5tdF9wYXJ0aXRpb25fdHVwbGVfcm91dGluZykKIAkJCXsKIAkJCQlsZWZ0
b3ZlclNsb3QgPSBleGVjdXRlX2F0dHJfbWFwX3Nsb3QobWFwLT5hdHRyTWFwLAogCQkJCQkJCQkJ
CQkJCSBvbGR0dXBsZVNsb3QsCmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVk
L2Zvcl9wb3J0aW9uX29mLm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvZm9yX3BvcnRp
b25fb2Yub3V0CmluZGV4IDMxZjc3MmM3Li4yNTBlMzY1MyAxMDA2NDQKLS0tIGEvc3JjL3Rlc3Qv
cmVncmVzcy9leHBlY3RlZC9mb3JfcG9ydGlvbl9vZi5vdXQKKysrIGIvc3JjL3Rlc3QvcmVncmVz
cy9leHBlY3RlZC9mb3JfcG9ydGlvbl9vZi5vdXQKQEAgLTIwOTcsNCArMjA5Nyw0NCBAQCBTRUxF
Q1QgKiBGUk9NIHRlbXBvcmFsX3BhcnRpdGlvbmVkXzUgT1JERVIgQlkgaWQsIHZhbGlkX2F0Owog
KDQgcm93cykKIAogRFJPUCBUQUJMRSB0ZW1wb3JhbF9wYXJ0aXRpb25lZDsKKy0tIFVQREFURSBG
T1IgUE9SVElPTiBPRiB3aXRoIHRhYmxlIGluaGVyaXRhbmNlCistLSBMZWZ0b3ZlciByb3dzIG11
c3Qgc3RheSBpbiB0aGUgY2hpbGQgdGFibGUsIHByZXNlcnZpbmcgY2hpbGQtc3BlY2lmaWMgY29s
dW1ucy4KK0NSRUFURSBUQUJMRSBmcG9faW5oX3BhcmVudCAoCisgIGlkIGludDRyYW5nZSwKKyAg
dmFsaWRfYXQgZGF0ZXJhbmdlLAorICBuYW1lIHRleHQKKyk7CitDUkVBVEUgVEFCTEUgZnBvX2lu
aF9jaGlsZCAoCisgIGRlc2NyaXB0aW9uIHRleHQKKykgSU5IRVJJVFMgKGZwb19pbmhfcGFyZW50
KTsKK0lOU0VSVCBJTlRPIGZwb19pbmhfY2hpbGQgKGlkLCB2YWxpZF9hdCwgbmFtZSwgZGVzY3Jp
cHRpb24pIFZBTFVFUworICAoJ1sxLDIpJywgJ1syMDE4LTAxLTAxLDIwMTktMDEtMDEpJywgJ29u
ZScsICdpbml0aWFsJyk7CistLSBVcGRhdGUgdGFyZ2V0cyB0aGUgcGFyZW50OyB0aGUgbWF0Y2hp
bmcgcm93IGxpdmVzIGluIHRoZSBjaGlsZC4KK1VQREFURSBmcG9faW5oX3BhcmVudCBGT1IgUE9S
VElPTiBPRiB2YWxpZF9hdCBGUk9NICcyMDE4LTA0LTAxJyBUTyAnMjAxOC0xMC0wMScKKyAgU0VU
IG5hbWUgPSAnb25lXjEnOworLS0gQWxsIHRocmVlIHJvd3Mgc2hvdWxkIGJlIGluIHRoZSBjaGls
ZCwgd2l0aCBkZXNjcmlwdGlvbiBwcmVzZXJ2ZWQuCitTRUxFQ1QgdGFibGVvaWQ6OnJlZ2NsYXNz
LCAqIEZST00gZnBvX2luaF9wYXJlbnQgT1JERVIgQlkgdmFsaWRfYXQ7CisgICB0YWJsZW9pZCAg
ICB8ICBpZCAgIHwgICAgICAgIHZhbGlkX2F0ICAgICAgICAgfCBuYW1lICAKKy0tLS0tLS0tLS0t
LS0tLSstLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLQorIGZwb19pbmhf
Y2hpbGQgfCBbMSwyKSB8IFsyMDE4LTAxLTAxLDIwMTgtMDQtMDEpIHwgb25lCisgZnBvX2luaF9j
aGlsZCB8IFsxLDIpIHwgWzIwMTgtMDQtMDEsMjAxOC0xMC0wMSkgfCBvbmVeMQorIGZwb19pbmhf
Y2hpbGQgfCBbMSwyKSB8IFsyMDE4LTEwLTAxLDIwMTktMDEtMDEpIHwgb25lCisoMyByb3dzKQor
CitTRUxFQ1QgKiBGUk9NIGZwb19pbmhfY2hpbGQgT1JERVIgQlkgdmFsaWRfYXQ7CisgIGlkICAg
fCAgICAgICAgdmFsaWRfYXQgICAgICAgICB8IG5hbWUgIHwgZGVzY3JpcHRpb24gCistLS0tLS0t
Ky0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLSstLS0tLS0tLS0tLS0tCisgWzEsMikg
fCBbMjAxOC0wMS0wMSwyMDE4LTA0LTAxKSB8IG9uZSAgIHwgaW5pdGlhbAorIFsxLDIpIHwgWzIw
MTgtMDQtMDEsMjAxOC0xMC0wMSkgfCBvbmVeMSB8IGluaXRpYWwKKyBbMSwyKSB8IFsyMDE4LTEw
LTAxLDIwMTktMDEtMDEpIHwgb25lICAgfCBpbml0aWFsCisoMyByb3dzKQorCistLSBObyByb3dz
IHNob3VsZCBoYXZlIGxlYWtlZCBpbnRvIHRoZSBwYXJlbnQuCitTRUxFQ1QgKiBGUk9NIE9OTFkg
ZnBvX2luaF9wYXJlbnQgT1JERVIgQlkgdmFsaWRfYXQ7CisgaWQgfCB2YWxpZF9hdCB8IG5hbWUg
CistLS0tKy0tLS0tLS0tLS0rLS0tLS0tCisoMCByb3dzKQorCitEUk9QIFRBQkxFIGZwb19pbmhf
cGFyZW50IENBU0NBREU7CitOT1RJQ0U6ICBkcm9wIGNhc2NhZGVzIHRvIHRhYmxlIGZwb19pbmhf
Y2hpbGQKIFJFU0VUIGRhdGVzdHlsZTsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3Fs
L2Zvcl9wb3J0aW9uX29mLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2Zvcl9wb3J0aW9uX29m
LnNxbAppbmRleCBkNDA2MmFjZi4uYmI3YzgwNDYgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jl
c3Mvc3FsL2Zvcl9wb3J0aW9uX29mLnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9mb3Jf
cG9ydGlvbl9vZi5zcWwKQEAgLTEzNjUsNCArMTM2NSwzMCBAQCBTRUxFQ1QgKiBGUk9NIHRlbXBv
cmFsX3BhcnRpdGlvbmVkXzUgT1JERVIgQlkgaWQsIHZhbGlkX2F0OwogCiBEUk9QIFRBQkxFIHRl
bXBvcmFsX3BhcnRpdGlvbmVkOwogCistLSBVUERBVEUgRk9SIFBPUlRJT04gT0Ygd2l0aCB0YWJs
ZSBpbmhlcml0YW5jZQorLS0gTGVmdG92ZXIgcm93cyBtdXN0IHN0YXkgaW4gdGhlIGNoaWxkIHRh
YmxlLCBwcmVzZXJ2aW5nIGNoaWxkLXNwZWNpZmljIGNvbHVtbnMuCisKK0NSRUFURSBUQUJMRSBm
cG9faW5oX3BhcmVudCAoCisgIGlkIGludDRyYW5nZSwKKyAgdmFsaWRfYXQgZGF0ZXJhbmdlLAor
ICBuYW1lIHRleHQKKyk7CitDUkVBVEUgVEFCTEUgZnBvX2luaF9jaGlsZCAoCisgIGRlc2NyaXB0
aW9uIHRleHQKKykgSU5IRVJJVFMgKGZwb19pbmhfcGFyZW50KTsKK0lOU0VSVCBJTlRPIGZwb19p
bmhfY2hpbGQgKGlkLCB2YWxpZF9hdCwgbmFtZSwgZGVzY3JpcHRpb24pIFZBTFVFUworICAoJ1sx
LDIpJywgJ1syMDE4LTAxLTAxLDIwMTktMDEtMDEpJywgJ29uZScsICdpbml0aWFsJyk7CisKKy0t
IFVwZGF0ZSB0YXJnZXRzIHRoZSBwYXJlbnQ7IHRoZSBtYXRjaGluZyByb3cgbGl2ZXMgaW4gdGhl
IGNoaWxkLgorVVBEQVRFIGZwb19pbmhfcGFyZW50IEZPUiBQT1JUSU9OIE9GIHZhbGlkX2F0IEZS
T00gJzIwMTgtMDQtMDEnIFRPICcyMDE4LTEwLTAxJworICBTRVQgbmFtZSA9ICdvbmVeMSc7CisK
Ky0tIEFsbCB0aHJlZSByb3dzIHNob3VsZCBiZSBpbiB0aGUgY2hpbGQsIHdpdGggZGVzY3JpcHRp
b24gcHJlc2VydmVkLgorU0VMRUNUIHRhYmxlb2lkOjpyZWdjbGFzcywgKiBGUk9NIGZwb19pbmhf
cGFyZW50IE9SREVSIEJZIHZhbGlkX2F0OworU0VMRUNUICogRlJPTSBmcG9faW5oX2NoaWxkIE9S
REVSIEJZIHZhbGlkX2F0OworLS0gTm8gcm93cyBzaG91bGQgaGF2ZSBsZWFrZWQgaW50byB0aGUg
cGFyZW50LgorU0VMRUNUICogRlJPTSBPTkxZIGZwb19pbmhfcGFyZW50IE9SREVSIEJZIHZhbGlk
X2F0OworCitEUk9QIFRBQkxFIGZwb19pbmhfcGFyZW50IENBU0NBREU7CisKIFJFU0VUIGRhdGVz
dHlsZTsK
--000000000000ff19ac064edb69da--