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. :-(