Hi,
I'm new to this list, and sorry if someone asked this question before. A
simplified version of the problem is presented below:
I have a table named xreflinks, which has 3 columns xrefid, objid, objtypeid
and all 3 columns together were assigned as primary key.
In the table there is already a tuple (1,1,1).
In a transaction I have the following statements:
delete from xreflinks where objid=1 and objtypeid=1;
...
...
select objid from xreflinks where objid=1 and objtypeid=1 and xrefid=1; (I
am using this statement to make sure I can insert this new tuple, which
unfortunately has the same xrefid as the tuple existed before transaction)
... # now I check if objid is non-zero, if it is null, do
insert into xreflinks (objid, objtypeid, xrefid) values(1,1,1);
The problem is, everything went OK till it got to the point of insertion,
where the pkey caused it to fail complaining of duplicate keys.
It seems the problem is that in transaction (I'm using read committed)
select sees that everything with (x,1,1) were deleted so that objid returned
by select is null, but the pkey referential integrity guard still sees the
real, uncommitted situation where (1,1,1) is still in the table, thus caused
the failure of the insertion. I had consulted with postgres documentation
about transaction/isolation levels and noticed that it only talked about
update, delete, select for update being same as select, while did not
discuss insertion into a table with some referential integraity guards.
I wonder if any of you knows how to solve this problem (not on application
level, since I can do it that way although it's more appropriate to have
postgres taking care of it).
Thanks a lot in advance!
Mingyi
