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
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 |