Re: BUG #13723: "duplicate key" error occurs when update delete and insert the same row concurrently

chjischj(at)163(dot)com writes:
> When i run sysbench's complex test with PostgreSQL, the following error
> always occured.
> duplicate key value violates unique constraint "%s"

> It seems to be a bug which occurs when executing update,delete and
> insert(within one transaction) the same row concurrently.

I see no bug here; you're just making a mistaken assumption about how
cross-transaction serialization works. At some point you're going to end
up with a timing in which both clients are trying to do the DELETE. Only
one does it; the other waits for that row change to commit, sees it's
done, and concludes that there's nothing for it to do. (In particular,
it will not see the row that was inserted later in the other transaction,
because that's too new.) Now the second one's INSERT fails because
there's already a row with id=1.

If you want this sort of coding to execute stably, you could consider
taking out a table-level lock, or some other way of preventing clients
from concurrently deleting+inserting the same key. Or, just don't do
that in the first place.

regards, tom lane