pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

insert or update violates foreign key constraint.why?
2+ messages / 2 participants
[nested] [flat]

* insert or update violates foreign key constraint.why?
@ 2004-05-28 13:49  Oleg Mayevskiy <oleg.mayevskiy@s2002.tu-chemnitz.de>
  0 siblings, 1 reply; 2+ messages in thread

From: Oleg Mayevskiy @ 2004-05-28 13:49 UTC (permalink / raw)
  To: pgsql-sql

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) REFERENCES public.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
'
DECLARE
	
	mission RECORD;
	
	ret bool;
	
	
BEGIN
ret:=FALSE;
raise notice\'begin\';
	
	SET CONSTRAINTS ALL IMMEDIATE;


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

	FOR mission IN SELECT * FROM public.test1
	LOOP
		raise notice\'after update\';		
		DELETE FROM public.test1 WHERE id=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. :-(



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: insert or update violates foreign key constraint.why?
@ 2004-06-01 02:05  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Oleg Mayevskiy <oleg.mayevskiy@s2002.tu-chemnitz.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-06-01 02:05 UTC (permalink / raw)
  To: Oleg Mayevskiy <oleg.mayevskiy@s2002.tu-chemnitz.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2004-06-01 02:05 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-28 13:49 insert or update violates foreign key constraint.why? Oleg Mayevskiy <oleg.mayevskiy@s2002.tu-chemnitz.de>
2004-06-01 02:05 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox