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
