Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Thu, Jan 2, 2014 at 2:37 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> Locking the definitely visible row only works if there's a row matching
> the index's columns. If the values of the new row don't have
> corresponding values in all the indexes you have the same old race
> conditions again.

I still don't get it - perhaps you should break down exactly what you
mean with an example. I'm talking about potentially doing multiple
upserts per row proposed for insertion to handle multiple conflicts,
perhaps with some deletes between upserts, not just one upsert with a
single update part.

> I think to be useful for many cases you really need to be able to ask
> for a potentially conflicting row and be sure that if there's none you
> are able to insert the row separately.

Why? What work do you need to perform after reserving the right to
insert but before inserting? Can't you just upsert resulting in
insert, and then perform that work, potentially deleting the row
inserted if and when you change your mind? Is there any real
difference between what that does for you, and what any particular
variety of promise tuple might do for you?

--
Peter Geoghegan