Hi,
While testing UPDATE FOR PORTION OF, I found a bug with inheritance tables. The following repro shows the problem more
clearlythan 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
leftoversneed to be inserted through the root relation for partition routing. Plain inheritance is different, leftover
rowsshould 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’sactual 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
goingto add it to the open items.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
