From: Dirk Jagdmann <jagdmann@gmail.com>
To: pgsql-sql@postgresql.org
Subject: foreign keys with on delete cascade and triggers
Date: Wed, 11 Jan 2006 23:56:40 +0100
Message-ID: <5d0f60990601111456m136aff2se77e354354a1c6aa@mail.gmail.com> (raw)
Hello,
I often create foreign keys with "on delete cascade" so I can
conviniently delete rows in multiple tables which are referenced by (a
chain) of foreign keys. Now I've run into an issue and I'd like to
have some opinions if the current behaviour of PostgreSQL is desired.
If have made my tests with versions 8.0.4 and 8.1.1.
The idea behind the sample commands below is, that the whole deletion
should be denied, because a trigger in a cascaded table blocked the
deletion. The trigger works as expected and prevents rows with a value
of "5" being deleted from table "b". However if the deletion was
triggered via the cascaded foreign key (trigger), the deletion in
table "a" is not rolled back, thus the row with "5" in "a" is lost.
This of course leaves the database in an inconsistant state, because
the foreign key in table "b" can no longer be referenced in "a".
Now I'd like to know if this is a bug in the current form of cascaded
deletions; or if this is desired behaviour and the oppression of
deletions via triggers is undefined behaviour in the cascaded case; or
if this issue just hasn't been addressed yet; or something completly
differnt.
create table a ( i int primary key );
create table b ( f int references a on delete cascade on update cascade );
create or replace function f() returns trigger as $$
BEGIN
IF OLD.f = 5 THEN
RETURN NULL;
END IF;
RETURN OLD;
END;
$$ language plpgsql;
create trigger b_del before delete on b for each row execute procedure f();
insert into a values(5);
insert into b values(5);
delete from a where i=5;
select * from a; -- 0 rows
select * from b; -- 1 row containing '5'
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jagdmann@gmail.com
Subject: Re: foreign keys with on delete cascade and triggers
In-Reply-To: <5d0f60990601111456m136aff2se77e354354a1c6aa@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox