pl/pgsql & fk constraint
От | Sindunata Sudarmaji |
---|---|
Тема | pl/pgsql & fk constraint |
Дата | |
Msg-id | 20030114150730.A22412@top4 обсуждение исходный текст |
Ответы |
Re: pl/pgsql & fk constraint
|
Список | 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 по дате отправления: