Hi,
On Tue, Dec 15, 2020 at 12:01 AM Arne Roland <A.Roland@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
issuehere. To me this doesn't seem to be a RI-trigger issue, but a more general issue like I mentioned in the pg-bugs
threadhttps://www.postgresql.org/message-id/b1bfc99296e34725900bcd9689be8420@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
partitionedtables 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
