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

On Thu, Sep 25, 2014 at 2:17 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> 1. You don't accept that value locks must be easily released in the
>> event of a conflict. Is anyone in this camp? It's far from obvious to
>> me what side of this question Andres is on at this stage, for example.
>> Robert might have something to say here too.
>>
>> 2. Having taken into account the experience of myself and Heikki, and
>> all that is implied by taking that approach ***while avoiding
>> unprincipled deadlocks***, you continue to believe that an approach
>> based on speculative heap insertion, or some alternative scheme is
>> better than what I have done to the nbtree code here, or you otherwise
>> dislike something about the proposed value locking scheme. You accept
>> that value locks must be released and released easily in the event of
>> a conflict, but like Heikki you just don't like what I've done to get
>> there.
>>
>> Since we can (I believe) talk about the value locking aspect and the
>> rest of the patch independently, we should do so...unless you're in
>> camp 1, in which case I guess that we'll have to thrash it out.
>
> I'm trying to understand and help out with pushing this patch forwards
> to completion.
>
> 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.

I laughed when I read this, because I think a lot of the discussion on
this topic has been unnecessarily muddled by jargon.

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

I think that something like this might work, but the devil is in the
details.  Suppose two people try to upsert into the same table at the
same time.  There's one index.  If the transactions search that index
for conflicts first, neither sees any conflicting tuples, and both
proceed.  That's no good.  OK, so suppose each transaction inserts the
special index tuple which you mention, to lock out concurrent inserts
of that value, and then searches for already-existing conflicts.  Each
sees the other's tuple, and they deadlock.  That's no good, either.

Also, I think there are other cases where we think we're going to
insert, so we put the special index tuple in there, but then we decide
to update, so we don't need the promise tuple any more, but other
sessions are potentially still waiting for our XID to terminate even
though there's no conflict any more.  I'm having a hard time bringing
the details of those cases to mind ATM, though.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company