| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables |
| Date: | 2026-05-07 03:40:12 |
| Message-ID: | 4245F94D-84F1-4E05-BF81-C458A6CF9901@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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/
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Fix-FOR-PORTION-OF-leftovers-for-inheritance-chil.patch | application/octet-stream | 14.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | cca5507 | 2026-05-07 04:04:44 | Re: Why is_admin_of_role() use ROLERECURSE_MEMBERS rather than ROLERECURSE_PRIVS? |
| Previous Message | Amul Sul | 2026-05-07 03:33:14 | Re: Question: Should we release the FK fast-path pk_slot's buffer pin promptly? |