Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Thu, Sep 25, 2014 at 11:17 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Basically, I have absolutely no idea whether I object to or agree with
> 1) and don't know where to look to find out. We need a clear
> exposition of design and the alternatives.
>
> My approach would be to insert an index tuple for that value into the
> index, but with the leaf ituple marked with an xid rather than a ctid.
> If someone tries to insert into the index they would see this and wait
> for the inserting transaction to end. The inserting transaction would
> then resolve what happens in the heap (insert/update) and later
> repoint the index tuple to the inserted/updated row version. I don't
> see the need for page level locking since it would definitely result
> in deadlocks (e.g. SQLServer).

The page level locks are only used to prevent concurrent insertion for
as long as it takes to get consensus to proceed among unique indexes,
and to actually insert a heap tuple. They're all released before we
lock the tuple for update, should we take that path (yes, really).
This is consistent with the behavior of other systems, I think. That's
my whole reason for preferring to do things that way. If you have a
"promise tuples" approach - be it what you outline here, or what
Heikki prototyped with heap tuple insertion, or any other - then you
need a way to *release* those "value locks" in the event of a
conflict/needing to update, before locking/updating. Otherwise, you
get deadlocks. This is an issue I highlighted when it came up with
Heikki's prototype.

AFAICT, any scheme for "value locking" needs to strongly consider the
need to *release* value locks inexpensively. Whatever else they do,
they cannot persist for the duration of the transaction IMV.

Does that make sense? If not, my next suggestion is applying an
earlier revision of Heikki's prototype, and seeing for yourself how it
can be made to deadlock in an unprincipled/impossible to prevent way
[1]. You've quite rightly highlighted the existing subxact looping
pattern as something that this needs to be better than in every way.
This is one important way in which we might fail to live up to that
standard.

[1] http://www.postgresql.org/message-id/52B4AAF0.5090806@vmware.com
--
Peter Geoghegan