Thread: transaction problem (delete/select/insert sequence fails, a bug?)

Hi, sorry to resend this mail but I wasn't sure if the first one wasn't read
because I forgot to put a subject there

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 it's a bug or there's a hidden constraint that prevented such
implementation?

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

"Liu, Mingyi" <mingyi.liu@gpc-biotech.com> writes:
> 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.

No, it does not.  I'm not sure what your problem is, but it's almost
certainly programmer error on your part.  Maybe you should be taking
another look at how you check the result of the SELECT?  I'd expect
you should check for "no rows returned", not null-ness of a column
value.

regression=# create table xreflinks(xrefid int, objid int, objtypeid int,
regression(# primary key (xrefid, objid, objtypeid));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'xreflinks_pkey' for table 'xreflinks'
CREATE TABLE
regression=# insert into xreflinks (objid, objtypeid, xrefid) values(1,1,1);
INSERT 2503215 1
regression=# begin;
BEGIN
regression=# delete from xreflinks where objid=1 and objtypeid=1;
DELETE 1
regression=# select objid from xreflinks where objid=1 and objtypeid=1 and xrefid=1;
 objid
-------
(0 rows)

regression=# insert into xreflinks (objid, objtypeid, xrefid) values(1,1,1);
INSERT 2503216 1
regression=# end;
COMMIT
regression=#

It also seems possible that you have two concurrent transactions doing
this at the same time, in which case the fact that their selects both
return zero rows won't prevent one of them from failing.

            regards, tom lane