Hi, On Tue, Dec 15, 2020 at 12:01 AM Arne Roland <a.rol...@index.de> wrote: > thanks for looking into this. I haven't yet looked at your patch in detail, > yet I think we should address the general issue here. To me this doesn't seem > to be a RI-trigger issue, but a more general issue like I mentioned in the > pg-bugs thread > https://www.postgresql.org/message-id/b1bfc99296e34725900bcd9689be8...@index.de
Hmm, maybe you're reading too much into the implementation details of the fix, because the patch does fix the issue that you mention in the linked report: Quoting your original example: drop table a, b; create table a (id serial, primary key (id)) partition by range (id); create table b (id serial, primary key (id)) partition by range (id); alter table b add constraint a_fk foreign key (id) references a (id) on delete cascade; create table a1 partition of a for values from (1) to (2); create table a2 partition of a for values from (2) to (3); create table b1 partition of b for values from (1) to (2); create table b2 partition of b for values from (2) to (3); insert into a (id) values (1); insert into b (id) values (1); -- correctly errors out instead of silently performing the ON DELETE CASCADE update a set id=2; ERROR: update or delete on table "a" violates foreign key constraint "a_fk" on table "b" DETAIL: Key (id)=(1) is still referenced from table "b". select * from b; id ---- 1 (1 row) Changing the example to specify ON UPDATE CASCADE: drop table a, b; create table a (id serial, primary key (id)) partition by range (id); create table b (id serial, primary key (id)) partition by range (id); alter table b add constraint a_fk foreign key (id) references a (id) on delete cascade; create table a1 partition of a for values from (1) to (2); create table a2 partition of a for values from (2) to (3); create table b1 partition of b for values from (1) to (2); create table b2 partition of b for values from (2) to (3); insert into a (id) values (1); insert into b (id) values (1); -- correctly applies ON UPDATE CASCADE action update a set id=2; UPDATE 1 select * from b; id ---- 2 (1 row) What am I missing about what you think is the more general problem to be solved? > While I like the idea of making fks work, I'd prefer a solution that fires > the appropriate row trigger for partitioned tables for non RI-cases as well. Maybe we could do that, but I don't know of a known issue where the root cause is our firing of a row trigger on the leaf partition instead of on the root partitioned table. -- Amit Langote EDB: http://www.enterprisedb.com
