Обсуждение: foreign key violation
i have 2 tables.
the first table "TAB1", has a primary key "id", which is the foreign key in the second table "TAB2"
i have 2 stored procedures,
1 stored procedure does
check if there is a row already present in TAB1 with col 2 = $1;
if not present
then
insert into TAB1(col2) values ($1 );
select into "myid" tab1_id from TAB1 where col2 = $1;
return myid
end if
please note that tab1_id is a serial bigint.
second stored procedure does
check if there is a row already present in TAB2 with col 2 = $2;
if not present
then
insert into TAB2 (col2, col3) values ($1 , $2);
select into "myid" tab2_id from TAB2 where col2 = $1;
return myid
end if
please note that tab2_id is the seriesl bigint.
also the second argument $2, is the value for the field col3, which is THE FOREIGN KEY.
in my transaction i do the following
begin
id = call stored procedure 1("abc");
id2 = call stored procedure 2 ("something", id);
commit
however i may have another transaction going in parallel, where i do this
delete from TAB1 where tab1_id = 5; //5 is just some value
in such a case sometime a foreign key violation occurs when the stored procedure 2 is called.
Is there any way i can avoid this problem?
thanks,
regards
Surabhi
surabhi.ahuja wrote:
>
> however i may have another transaction going in parallel, where i do this
>
> delete from TAB1 where tab1_id = 5; //5 is just some value
>
> in such a case sometime a foreign key violation occurs when the stored procedure 2 is called.
>
> Is there any way i can avoid this problem?
It's not a problem, it's what's supposed to happen. You've just deleted
the row it's trying to reference. One of the two operations needs to fail.
There are two options:
1. Catch the error and retry whichever operation fails (the
function-calls in this case).
2. Use locking to ensure the operation isn't disturbed:
begin, lock table, call functions, commit
This will prevent anyone else from modifying the locked table while your
transaction is modifying it. You might be able to get away with less
extensive locking in your particular case, but for full details see the
chapter on "Concurrency Control":
http://www.postgresql.org/docs/8.1/static/mvcc.html
HTH
--
Richard Huxton
Archonet Ltd