Hi,
Robert forwarded me a pgsql-general thread [1] where a ON DELETE
CASCADE specified on a foreign key pointing to a partitioned table is
shown to cause a possibly surprising end result during an update of
the partitioned table. Example from that thread:
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);
insert into parent values(0);
insert into child values(1,0);
update parent set id = 5; -- no row movement, so normal update
table parent;
id
----
5
(1 row)
table child;
id | parent_id
----+-----------
1 | 5
(1 row)
update parent set id = 15; -- row movement, so delete+insert
table parent;
id
----
15
(1 row)
table child; -- ON DELETE CASCADE having done its job
id | parent_id
----+-----------
(0 rows)
Reporter on that thread says that the last update should have failed
and I don't quite see a workable alternative to that. What we could
do is check before calling ExecDelete() that will perform the DELETE
part of the row movement if the foreign key action trigger that
implements the ON DELETE CASCADE action (an internal trigger) is among
the AR delete triggers that will run as part of that DELETE. If yes,
abort the operation. See attached a patch for that. I'm not terribly
happy with the error and details messages though:
update parent set id = 15;
ERROR: cannot move row being updated to another partition
DETAIL: Moving the row may cause a foreign key involving the source
partition to be violated.
Thoughts?
--
Amit Langote
EDB: http://www.enterprisedb.com
[1] https://www.postgresql.org/message-id/flat/CAL54xNZsLwEM1XCk5yW9EqaRzsZYHuWsHQkA2L5MOSKXAwviCQ%40mail.gmail.com