public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Re: Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables
Date: Sat, 9 May 2026 13:47:22 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[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/
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: Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables
In-Reply-To: <[email protected]>
* 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