DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Uk1j8mswNx+tw0XZKtCCECYROr+1mxmYN9MwZX1LbE89adDQbGIWHtXjUxk7/trJOrW8QJxCH2dWBPC3uXD0Ob8Vk125IAgmKJ8KFelHu9hZrQ4MbPe97nMorrzNChD0nTI+PGOrBaawk0aLmhvJRghA0b1NkpwULVesmyzyWcw=
Message-ID: <5d0f60990601120349x11df4f30yfcbfbb7e2adae227@mail.gmail.com>
Date: Thu, 12 Jan 2006 12:49:27 +0100
From: Dirk Jagdmann <jagdmann@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: foreign keys with on delete cascade and triggers
Cc: pgsql-sql@postgresql.org
In-Reply-To: <331.1137034941@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
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=3DOLD.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=3D1; -- will raise the notice
delete from a where i=3D2; -- 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