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

On 2013-12-27 14:11:44 -0800, Peter Geoghegan wrote:
> On Fri, Dec 27, 2013 at 12:57 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > I don't think the current syntax the feature implements can be used as
> > the sole argument what the feature should be able to support.
> >
> > If you think from the angle of a async MM replication solution
> > replicating a table with multiple unique keys, not having to specify a
> > single index we to expect conflicts from, is surely helpful.
>
> 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.

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

If there are multiple conflicts the conflicting row should be
updated. If we didn't release the value locks on the individual indexes,
we can know beforehand whether only one row is going to be affected. If
there really are more than one, error out.

Greetings,

Andres Freund

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