From: Dirk Jagdmann <jagdmann@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: foreign keys with on delete cascade and triggers
Date: Thu, 12 Jan 2006 12:49:27 +0100
Message-ID: <5d0f60990601120349x11df4f30yfcbfbb7e2adae227@mail.gmail.com> (raw)
In-Reply-To: <331.1137034941@sss.pgh.pa.us>
References: <5d0f60990601111456m136aff2se77e354354a1c6aa@mail.gmail.com>
<331.1137034941@sss.pgh.pa.us>
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
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, tgl@sss.pgh.pa.us
Subject: Re: foreign keys with on delete cascade and triggers
In-Reply-To: <5d0f60990601120349x11df4f30yfcbfbb7e2adae227@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