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!