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

On Thu, Sep 25, 2014 at 12:11 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

I'm very glad that you share my concern about deadlocks like this.

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

Well, you might have a promise tuple in a unique index on attributes
not appearing in the UPDATE's targetlist, for one. You have the other
session waiting (doesn't have to be an upserter) just because we
*thought about* inserting a value as part of an upsert. That's pretty
bad.
--
Peter Geoghegan