On Thu, Sep 25, 2014 at 1:21 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> 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.
(by page lock, I take it you mean buffer lock - converting that into a
page hwlock is what I do).
This is where it gets quite complicated. What happens if row locking
on upsert finds a conflict update changing uniquely-constrained
attributes? Sure, a vanilla non-HOT update will fail on inserting a
unique index tuple, but *it* can still cause us a row-level conflict,
and *it* can only fail (with a dup violation) when we commit/abort.
But now we're obligated to wait on it to get the row lock, and it's
obligated to wait on us to get the promise tuple lock, or any other
sort of "value lock" that hasn't already been released when we go to
row lock. Deadlock.
You cannot get away with failing to release the promise tuple/value
lock if you want to maintain useful guarantees.
It doesn't need to be a vanilla non-HOT update. That's just the
simplest example I can think of.
>> 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.
Sure, you can throw an error, and that makes things a lot easier. It
also implies that the implementation is inferior to the subxact
looping pattern, which you've already implied is a thing we must beat
in every way. Frankly, I think it's a cop-out to just throw an error,
and I don't think it'll end up being some theoretical risk. It'll
happen often if it is allowed to happen at all. Allowing it to happen
almost defeats the purpose of the feature - the big appeal of the
feature is that it makes guarantees about the outcome.
--
Peter Geoghegan