BUG #10856: Delete trigger corrupts foreign key integrity
От | m.fritz@wisutec.de |
---|---|
Тема | BUG #10856: Delete trigger corrupts foreign key integrity |
Дата | |
Msg-id | 20140704065535.5163.57724@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #10856: Delete trigger corrupts foreign key integrity
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 10856 Logged by: Mathias Fritz Email address: m.fritz@wisutec.de PostgreSQL version: 9.3.4 Operating system: Windows 7 x64 Description: Affected version: --------------------------------- "PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 64-bit" Problem description: --------------------------------- A "before delete" trigger on a child table returning "NULL" prevents records from deletion, which violates against the FOREIGN KEY constraint. Child records are still present, master record is deleted but FOREIGN KEY is still "VALID". Although this is a logical bug of the trigger (to return NULL in delete operation), there should be never orphaned records. Also the FK validity check seems to be ignoring the orphaned child records. Steps to reproduce: --------------------------------- drop table if exists child; drop table if exists master; -- Sample master table create table master(id_m integer, name character varying(100), constraint pk_master_id_m primary key(id_m)); -- Sample child table with cascading delete FK create table child(id_c integer, id_m integer, name character varying(100), constraint pk_child_id_c primary key(id_c), constraint fk_child_id_m foreign key(id_m) references master(id_m) on delete cascade); CREATE OR REPLACE FUNCTION trgfn_child() RETURNS trigger AS $BODY$ DECLARE tmpID_C integer; tmpID_M integer; BEGIN -- For TG_OP='DELETE' NEW is null, so trigger prevents further processing of row RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER tr_child BEFORE INSERT OR UPDATE OR DELETE ON child FOR EACH ROW EXECUTE PROCEDURE trgfn_child(); insert into master values (1, 'test 1'); insert into master values (2, 'test 2'); insert into child values (1, 1, 'child 1-1'); insert into child values (2, 1, 'child 1-2'); insert into child values (3, 2, 'child 2-1'); insert into child values (4, 2, 'child 2-2'); -- Start deleting master delete from master where id_m = 2; -- 1st bug: Cascading didn't work, there are still children, but master is deleted! select * from master where id_m = 2; /* 0 rows, good */ select * from child where id_m = 2; /* 2 rows still present !*/ -- 2nd bug: Master is gone, child present, but FK still valid alter table child validate constraint fk_child_id_m;
В списке pgsql-bugs по дате отправления: