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

On 25 September 2014 20:11, Robert Haas <robertmhaas@gmail.com> wrote:

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

The test index is unique, so our to-be-inserted value exists on only
one page, hence page locking applies while we insert it. The next
person to insert waits for the page lock and then sees the test tuple.

The page lock lasts only for the duration of the insertion of the
ituple, not for the whole operation.

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

We make the decision to INSERT or UPDATE based upon what we find in
the test index. If a value if there already, we assume its an UPDATE
and go to update the row this points to. If it has been deleted we
loop back and try again/error. If the value is not present, we insert
the test tuple and progress as an INSERT, then loop back later to set
the ctid. There is no case of "don't need promise id anymore". We
would use the PK, identity or first unique index as the test index.
There is a case where an UPSERT conflicts with an INSERT causing the
latter to abort.

Anyway, this is why we need the design more clearly exposed, so you
can tell me I'm wrong by showing me the URL of it done right.

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