unexpected effect of FOREIGN KEY ON CASCADE DELETE - Mailing list 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