Обсуждение: 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@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=#
"Tim Leppard" <hbug_1@hotmail.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