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

"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