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
prevent-row-movement-on-delete-cascade.patch
Description: Binary data
