pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedforeign keys with on delete cascade and triggers
4+ messages / 2 participants
[nested] [flat]
* foreign keys with on delete cascade and triggers
@ 2006-01-11 22:56 Dirk Jagdmann <jagdmann@gmail.com>
2006-01-12 03:02 ` Re: foreign keys with on delete cascade and triggers Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Dirk Jagdmann @ 2006-01-11 22:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: foreign keys with on delete cascade and triggers
2006-01-11 22:56 foreign keys with on delete cascade and triggers Dirk Jagdmann <jagdmann@gmail.com>
@ 2006-01-12 03:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-12 11:49 ` Re: foreign keys with on delete cascade and triggers Dirk Jagdmann <jagdmann@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-01-12 03:02 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: pgsql-sql
Dirk Jagdmann <jagdmann@gmail.com> writes:
> 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.
This is a bug in your trigger design. The database is doing what it's
supposed to do, ie, trying to delete the dependent row. If you turn
that into a no-op, it's your own fault if you don't like the resulting
state.
If you want the whole transaction rolled back, raise an error instead
of returning NULL.
(This has been discussed before ... see the archives.)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: foreign keys with on delete cascade and triggers
2006-01-11 22:56 foreign keys with on delete cascade and triggers Dirk Jagdmann <jagdmann@gmail.com>
2006-01-12 03:02 ` Re: foreign keys with on delete cascade and triggers Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-01-12 11:49 ` Dirk Jagdmann <jagdmann@gmail.com>
2006-01-12 15:46 ` Re: foreign keys with on delete cascade and triggers Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Dirk Jagdmann @ 2006-01-12 11:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hello Tom,
> If you want the whole transaction rolled back, raise an error instead
> of returning NULL.
You're right, that's working. But now I have a slightly different problem.
I assume that the trigger which watches the cascaded deletions first
deletes the row in the monitored table and then deletes any dependant
foreign keys. Thus the "foreign key tree" is deleted in a top-down
manner. This hinders any triggers on delete queries in cascaded tables
to query the referenced table any longer, since the referenced row is
already deleted. The following code shows what I mean:
create table a ( i serial primary key, name text );
create table b ( f int references a on delete cascade );
create or replace function f() returns trigger as $$
DECLARE
n text;
BEGIN
SELECT name INTO n from a where i=OLD.f;
IF FOUND THEN
RAISE NOTICE '% deleted me', n;
END IF;
RETURN OLD;
END;
$$ language plpgsql;
create trigger b_del before delete on b for each row execute procedure f();
insert into a(name) values('Dirk');
insert into b select currval('a_i_seq');
insert into a(name) values('Tom');
insert into b select currval('a_i_seq');
delete from b where f=1; -- will raise the notice
delete from a where i=2; -- wont raise anything
If the "foreign key tree" would be deleted in a bottom-up (or
depth-first) manner the second delete would be able to retrieve the
row in table a.
Now I'd like to know if the current order of deletions in PostgreSQL
is intended in the top-down way or if that could be changed?
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: foreign keys with on delete cascade and triggers
2006-01-11 22:56 foreign keys with on delete cascade and triggers Dirk Jagdmann <jagdmann@gmail.com>
2006-01-12 03:02 ` Re: foreign keys with on delete cascade and triggers Tom Lane <tgl@sss.pgh.pa.us>
2006-01-12 11:49 ` Re: foreign keys with on delete cascade and triggers Dirk Jagdmann <jagdmann@gmail.com>
@ 2006-01-12 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-01-12 15:46 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: pgsql-sql
Dirk Jagdmann <jagdmann@gmail.com> writes:
> Now I'd like to know if the current order of deletions in PostgreSQL
> is intended in the top-down way or if that could be changed?
Sorry, I don't see much chance of changing it.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-01-12 15:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-11 22:56 foreign keys with on delete cascade and triggers Dirk Jagdmann <jagdmann@gmail.com>
2006-01-12 03:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-12 11:49 ` Dirk Jagdmann <jagdmann@gmail.com>
2006-01-12 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
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