BUG #4437: Breaking referential integrity with a trigger

The following bug has been logged online:

Bug reference: 4437
Logged by: Tim Leppard
Email address: hbug_1(at)hotmail(dot)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=#

"Tim Leppard" <hbug_1(at)hotmail(dot)com> writes:
> Returning NULL from a BEFORE DELETE trigger function on a referencing table
> using CASCADE allows you to break RI.

Yup, so don't do that ;-). Actually there are any number of ways to
break an RI constraint with poorly designed triggers. The only way
we could prevent it is to make RI updates not fire triggers, which seems
a cure worse than the disease.

regards, tom lane