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
