From: Chao Li <li.evan.chao@gmail.com>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables
Date: Sat, 9 May 2026 13:47:22 +0800
References: <4245F94D-84F1-4E05-BF81-C458A6CF9901@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
In-Reply-To: <4245F94D-84F1-4E05-BF81-C458A6CF9901@gmail.com>
Message-Id: <260A544F-FBD6-40CC-9449-5987049DD2D4@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/260A544F-FBD6-40CC-9449-5987049DD2D4%40gmail.com>
Precedence: bulk


> On May 7, 2026, at 11:40, Chao Li <li.evan.chao@gmail.com> wrote:
>=20
> Hi,
>=20
> 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=3D# create table p (id int, valid_at daterange, name text);
> CREATE TABLE
> evantest=3D# create table c (extra text) inherits (p);
> CREATE TABLE
> evantest=3D# insert into c values (1, daterange('2000-01-01', =
'2010-01-01'), 'old', 'x');
> INSERT 0 1
> evantest=3D# update p for portion of valid_at from '2001-01-01' to =
'2002-01-01' set name =3D 'new' where id =3D 1;
> UPDATE 1
> evantest=3D# 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)
>=20
> evantest=3D# select * from only c;
> id |        valid_at         | name | extra
> ----+-------------------------+------+-------
>  1 | [2001-01-01,2002-01-01) | new  | x
> (1 row)
> ```
>=20
> 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.
>=20
> The same bug exists for DELETE FOR PORTION OF with inheritance tables =
as well:
> ```
> evantest=3D# delete from p for portion of valid_at from '2001-01-01' =
to '2002-01-01' where id =3D 1;
> DELETE 1
> evantest=3D# 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)
>=20
> evantest=3D# select * from only c;
> id | valid_at | name | extra
> ----+----------+------+-------
> (0 rows)
> ```
>=20
> 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.
>=20
> While debugging this, I also noticed another issue around mapping the =
range column=E2=80=99s attnum. In multiple-inheritance cases, the range =
column=E2=80=99s attnum in a child table may be different from the one =
in its parent, so we need to use the child=E2=80=99s actual attnum.
>=20
> Please see the attached patch for the fix details and the new tests. =
Since I believe this bug was introduced in 19, I=E2=80=99m going to add =
it to the open items.
>=20
> Best regards,
> --
> Chao Li (Evan)
> HighGo Software Co., Ltd.
> https://www.highgo.com/
>=20
>=20
>=20
>=20
> <v1-0001-Fix-FOR-PORTION-OF-leftovers-for-inheritance-chil.patch>

Merged into [1].

[1] =
https://postgr.es/m/CAHg+QDcd=3Dt69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=3DhM0=
g@mail.gmail.com

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