BUG #4437: Breaking referential integrity with a trigger - Mailing list pgsql-bugs

From Tim Leppard
Subject BUG #4437: Breaking referential integrity with a trigger
Date
Msg-id 200809251615.m8PGFhZh005689@wwwmaster.postgresql.org
Whole thread Raw
Responses Re: BUG #4437: Breaking referential integrity with a trigger
List pgsql-bugs
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=#

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: Incorrect "invalid AM/PM string" error from to_timestamp
Next
From: Tom Lane
Date:
Subject: Re: BUG #4437: Breaking referential integrity with a trigger