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;