foreign key violation

Started by surabhi.ahujaalmost 20 years ago2 messagesgeneral
Jump to latest
#1surabhi.ahuja
surabhi.ahuja@iiitb.ac.in

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

#2Richard Huxton
dev@archonet.com
In reply to: surabhi.ahuja (#1)
Re: foreign key violation

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