pl/pgsql & fk constraint - Mailing list pgsql-general

From Sindunata Sudarmaji
Subject pl/pgsql & fk constraint
Date
Msg-id 20030114150730.A22412@top4
Whole thread Raw
Responses Re: pl/pgsql & fk constraint
List pgsql-general
Hi all,

I think I found a bug in the combination of PL/PGSQL and Foreign Key
constraint. Please find below a sample case problem:

--------------------------------------------------------------------
create table test1 (
  f1   varchar(10) not null,
constraint test1_pk primary key (f1)
);

create table test2 (
  f1   varchar(10) not null,
  f2   varchar(5)  not null,
constraint test2_pk primary key (f1, f2),
constraint test2_test1_fk foreign key (f1) references test1(f1)
  on update cascade
);

insert into test1 values ('TEST');
insert into test2 values ('TEST', '001');

create or replace function transfertest() returns integer as '
begin
  delete from test2 where f1 = ''TEST'';
  delete from test1 where f1 = ''TEST'';
  insert into test1 values (''TEST'');
  insert into test2 values (''TEST'', ''002'');
  return 0;
end;
' language 'plpgsql';
--------------------------------------------------------------------

test=# select transfertest();
ERROR:  test2_test1_fk referential integrity violation - key in test1
still referenced from test2

however, running the command outside PLPGSQL works as expected:

test=# delete from test2 where f1='TEST'; delete from test1 where
f1='TEST'; insert into test1 values ('TEST'); insert into test2 values
('TEST','002');
DELETE 1
DELETE 1
INSERT 16578 1
INSERT 16579 1
test=# select * from test1;
  f1
------
 TEST
(1 row)

test=# select * from test2;
  f1  | f2
------+-----
 TEST | 002
(1 row)

If I didn't create the FK test2_test1_fk on table test2, it also worked
as expected. So this bug occurs on a combination of PLPGSQL and FK
constraint usage. Am I doing something wrong here? Or is it a known PG
bug?

I tested the bug on:
pppm=> select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

and

test=# select version();
                           version
--------------------------------------------------------------
 PostgreSQL 7.2.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
(1 row)

Please help,
TIA,
Sindu

pgsql-general by date:

Previous
From: "kanchana"
Date:
Subject: configure error with krb5
Next
From: Çağıl Şeker
Date:
Subject: DATESTYLE