Обсуждение: insert or update violates foreign key constraint.why?

it seems to be a simple problem, but it is not.
i have declared 2 simple tables:

CREATE TABLE public.test1
( id int4 NOT NULL, data float4, CONSTRAINT mytest_pkey PRIMARY KEY (id)
) WITH OIDS;

CREATE TABLE public.test2
( id1 int4 NOT NULL, data1 float4, CONSTRAINT test2_pkey PRIMARY KEY (id1), CONSTRAINT "$1" FOREIGN KEY (id1)
REFERENCESpublic.test1 (id) ON UPDATE 
 
CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;

then i have written a function in PLPGSQL:

CREATE OR REPLACE FUNCTION public.test_func() RETURNS bool AS
'
DECLAREmission RECORD;ret bool;
BEGIN
ret:=FALSE;
raise notice\'begin\';SET CONSTRAINTS ALL IMMEDIATE;

FOR mission IN SELECT * FROM public.test1LOOP    raise notice\'before update\';            UPDATE public.test2 SET
data1=data1+1;ENDLOOP;
 
FOR mission IN SELECT * FROM public.test1LOOP    raise notice\'after update\';            DELETE FROM public.test1
WHEREid=mission.id;END LOOP;
 







ret:=TRUE;

raise notice\'end\';


RETURN ret;

END;' LANGUAGE 'plpgsql' VOLATILE;

my expecting behavior is:
update all rows in test2
delete all from test1 und then delete all from test1 because of the ON 
DELETE CASCADE

BUT:

NOTICE:  begin
NOTICE:  before update
NOTICE:  before update
NOTICE:  before update
NOTICE:  after update
NOTICE:  after update
NOTICE:  after update
NOTICE:  end

ERROR:  insert or update on table "test2" violates foreign key constraint 
"$1"
DETAIL:  Key (id1)=(1) is not present in table "test1".

why?
i have asked already in postgresql chan for help, but nobody could solve 
the problem.
I hope you can.

Big THX

Oleg

PS: i tried NOT DEFERRABLE too, it does not work too. :-(

Oleg Mayevskiy <oleg.mayevskiy@s2002.tu-chemnitz.de> writes:
> it seems to be a simple problem, but it is not.

Yes it is ... you're expecting the RI triggers to fire during the
plpgsql function, but in fact they fire at completion of the outer
statement that called the plpgsql function.

There's been some debate about whether this is really the most
desirable behavior, but that's how it is at the moment.
        regards, tom lane