unexpected effect of FOREIGN KEY ON CASCADE DELETE

From: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: unexpected effect of FOREIGN KEY ON CASCADE DELETE
Date: 2010-06-23 12:35:52
Message-ID: AANLkTimMaAubbjElLBxuHFLBGzBwFKOLQm7fpHbHV09g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

consider following example:

CREATE TABLE foob(id serial primary key, name varchar default '');
CREATE TABLE fooA(id serial primary key, fooB int not null references
fooB(id) on update cascade on delete cascade, name varchar default
'');

CREATE FUNCTION foobarrA() RETURNS trigger AS
$_$
BEGIN
RAISE NOTICE 'foobarred %', (SELECT name FROM fooB WHERE id = OLD.fooB);
RETURN OLD;
END;
$_$ LANGUAGE 'plpgsql';

CREATE TRIGGER foobarrrred BEFORE DELETE ON fooA FOR EACH ROW EXECUTE
PROCEDURE foobarrA();
insert into foob(name) select random()::varchar FROM generate_series(1,100);
insert into fooa(name, foob) select random()::varchar, (select id from
foob order by random() limit 1) FROM generate_series(1,100);

select foob from fooa order by random() limit 1;
foob
------
70
(1 row)

DELETE FROM foob where id =70;
NOTICE: foobarred <NULL>
CONTEXT: SQL statement "DELETE FROM ONLY "public"."fooa" WHERE $1
OPERATOR(pg_catalog.=) "foob""
NOTICE: foobarred <NULL>

I always assumed, that since triggers are set to BEFORE, the data will
still exist in the tables when they are fired, it will still be
accessible. I looked in the manual, and there is no mention of that
effect anywhere I can find.

And here's the question, is there any way in which I can overcome that
(to me) problem ? Other than, by substituting foreign key with my own
trigger, to handle that situation and than delete data.

thank you .

--
GJ

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thom Brown 2010-06-23 13:22:11 Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE
Previous Message Vibhor Kumar 2010-06-23 11:38:07 Re: disable password prompt - command line