Fix bug of UPDATE/DELETE FOR PORTION OF with inheritance tables

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

Browse pgsql-hackers by date

  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?