Thread: BUG #14114: FK on-delete-cascade trouble

BUG #14114: FK on-delete-cascade trouble

From
zha-vova@yandex.ru
Date:
The following bug has been logged on the website:

Bug reference:      14114
Logged by:          Vladimir Zhavoronkov
Email address:      zha-vova@yandex.ru
PostgreSQL version: 9.4.5
Operating system:   Red Hat 4.8.5-4 64-bit
Description:

After creating a FK constraint with "on delete cascade" option I got a
problem with before-delete-trigger on a dependent table. This short listing
below will make it clear:

test_db=# create table t (id integer primary key);
CREATE TABLE
test_db=# create table tt (id integer primary key, t_id integer references
t(id) on delete cascade);
CREATE TABLE

--here we have table "t" that is referenced from table "tt" with
on-delete-cascade foreign key

--this before-trigger will prevent deleting rows from table "tt"

test_db=# create or replace
test_db-# function d_tt()
test_db-# returns trigger as $$
test_db$# begin
test_db$#   return null;
test_db$# end;
test_db$# $$ language plpgsql;
CREATE FUNCTION
test_db=# create trigger d_tt before delete on tt for each row execute
procedure d_tt();
CREATE TRIGGER

--insert some data in tables:
test_db=# insert into t(id) values(1);
INSERT 0 1
test_db=# insert into tt(id, t_id) values(1,1);
INSERT 0 1

--so we have a record in table "tt" that references table "t"; then we try
to delete referenced record from table "t":

test_db=# delete from t where id = 1;
DELETE 1

Now we deleted record with id=1 from table "t", but still got records in
table "tt" with t_id=1. This contradicts with our foreign key constraint:


test_db=# select * from t;
 id
----
(0 rows)


test_db=# select * from tt;
 id | t_id
----+------
  1 |    1
(1 row)

Re: BUG #14114: FK on-delete-cascade trouble

From
Tom Lane
Date:
zha-vova@yandex.ru writes:
> After creating a FK constraint with "on delete cascade" option I got a
> problem with before-delete-trigger on a dependent table. This short listing
> below will make it clear:

This is not a bug, this is expected behavior.  If you have a trigger that
interferes with the updates commanded by a foreign key cascade query,
you'll break the integrity of the FK.  The only way we could prevent that
is to not run triggers for FK cascade updates; which is a cure worse than
the disease, since there are plenty of legitimate reasons why you'd want
a table's triggers to fire.

Write your triggers more carefully.

            regards, tom lane