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

On Thu, Jan 2, 2014 at 1:49 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> Well, you're not totally on your own for something like that with this
>> feature. You can project the conflicter's tid, and possibly do a more
>> sophisticated recovery, like inspecting the locked row and iterating.
>
> Yea, but in that case I *do* conflict with more than one index and old
> values need to stay locked. Otherwise anything resembling
> forward-progress guarantee is lost.

I'm not sure I understand. In a very real sense they do stay locked.
What is insufficient about locking the definitively visible row with
the value, rather than the value itself? What distinction are you
making? On the first conflict you can delete the row you locked, and
then re-try, possibly further merging some stuff from the just-deleted
row when you next upsert.

It's possible that an "earlier" unique index value that is unlocked
before row locking proceeds will get a new would-be duplicate after
you're returned a locked row, but it's not obvious that that's a
problem for your use-case (a problem that can't be worked around), or
that promise tuples get you anything better.

>> That's probably not at all ideal, but then I can't even imagine what
>> the best interface for what you describe here looks like. If there are
>> multiple conflicts, do you delete or update some or all of them? How
>> do you express that concept from a DML statement?
>
> For my usecases just getting the tid back is fine - it's in C
> anyway. But I'd rather be in a position to do it from SQL as well...

I believe you can.

--
Peter Geoghegan