Hi developers, We have a strange case where some rows are removed. I think it is a bug, but before notifying it I prefer to ask here where I am wrong.
*Postgres 12* Given the following structure: create table parent ( id serial, constraint parent_pkey primary key (id)) partition by range (id); create table parent_10 partition of parent for values from (0) to (10); create table parent_20 partition of parent for values from (11) to (20); create table child ( id serial, parent_id int constraint parent_id_fk references parent(id) on update cascade on delete cascade); -- Notice the on update cascade on delete cascade. insert into parent values(0); insert into child values(1,0); -- Here are the rows postgres=# table parent; id ---- 0 (1 row) postgres=# table child; id | parent_id ----+----------- 1 | 0 (1 row) *-- Update the parent table id, with a value contained in the same partition* update parent set id = 5; postgres=# table parent; id ---- 5 (1 row) postgres=# table child; id | parent_id ----+----------- 1 | 5 (1 row) *-- Update the parent table, with a value contained into other partition* update parent set id = 15; postgres=# update parent set id = 15; UPDATE 1 postgres=# table parent; id ---- 15 (1 row) *postgres=# table child; id | parent_id----+-----------(0 rows)* No error or warning was thrown. The rows in the child table were removed. I think what has happened is: The update caused a DELETE in the table parent_10 (removing the rows from child table) and then the INSERT into parent_20. We've checked the documentation but didn't find something about this unexpected behaviour. Trying without "on delete cascade" clause throws a "parent key not found error". Thank you!
