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

On 2014-01-02 02:20:02 -0800, Peter Geoghegan wrote:
> 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?

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 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.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services