Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE

On Wednesday 23 June 2010 5:35:52 am Grzegorz Jaśkiewicz wrote:
> 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

My suspicion is that this is an identifier problem. See error below:
CONTEXT: SQL statement "DELETE FROM ONLY "public"."fooa" WHERE $1
OPERATOR(pg_catalog.=) "foob"" <--- ***

It would seem to me there is confusion between the table fooB(b) and the column
foob. I am afraid at this point I can not be any more helpful.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com