Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE

2010/6/23 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
> 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.

It is in there: http://www.postgresql.org/docs/8.4/static/sql-createtrigger.html

"SQL specifies that BEFORE DELETE triggers on cascaded deletes fire
after the cascaded DELETE completes. The PostgreSQL behavior is for
BEFORE DELETE to always fire before the delete action, even a
cascading one. This is considered more consistent. There is also
unpredictable behavior when BEFORE triggers modify rows that are
later to be modified by referential actions. This can lead to
constraint violations or stored data that does not honor the
referential constraint. "

But it sounds like it's not doing that.

Thom