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