The following bug has been logged online:
Bug reference: 4437
Logged by: Tim Leppard
Email address: hbug_1@hotmail.com
PostgreSQL version: 8.3.4
Operating system: Multiple
Description: Breaking referential integrity with a trigger
Details:
Returning NULL from a BEFORE DELETE trigger function on a referencing table
using CASCADE allows you to break RI. An example (plpgsql required):
test=# create table foo (x int primary key);
CREATE TABLE
test=# create table bar (x int primary key references foo on delete
cascade);
CREATE TABLE
test=# insert into foo values (1);
INSERT 0 1
test=# insert into bar values (1);
INSERT 0 1
test=# create function tf() returns trigger as $tf$ begin return new; end;
$tf$ language 'plpgsql';
CREATE FUNCTION
test=# create trigger trig before delete on bar for each row execute
procedure tf();
CREATE TRIGGER
test=# select * from foo;
x
---
1
(1 row)
test=# select * from bar;
x
---
1
(1 row)
test=# delete from foo;
DELETE 1
test=# select * from foo;
x
---
(0 rows)
test=# select * from bar;
x
---
1
(1 row)
test=# insert into bar values (2);
ERROR: insert or update on table "bar" violates foreign key constraint
"bar_x_fkey"
DETAIL: Key (x)=(2) is not present in table "foo".
test=#
