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

On Thu, Feb 19, 2015 at 11:10 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
>> I fully agree with your summary here. However, why should we suppose
>> that while we wait, the other backends don't both delete and then
>> re-insert their tuple? They need the pre-check to know not to
>> re-insert their tuple (seeing our tuple, immediately after we wake as
>> the preferred backend with the older XID) in order to break the race.
>> But today, exclusion constraints are optimistic in that the insert
>> happens first, and only then the check. The pre-check turns that the
>> other way around, in a limited though necessary sense.
>
> I'm not sure I understand exactly what you're saying, but AFAICS the
> pre-check doesn't completely solve that either. It's entirely possible that
> the other backend deletes its tuple, our backend then performs the
> pre-check, and the other backend re-inserts its tuple again. Sure, the
> pre-check reduces the chances, but we're talking about a rare condition to
> begin with, so I don't think it makes sense to add much code just to reduce
> the chances further.

But super deletion occurs *before* releasing the token lock, which is
the last thing we do before looping around and starting again. So iff
we're the oldest XID, the one that gets to "win" by unexpectedly
waiting on another's token in our second phase (second call to
check_exclusion_or_unique_constraint()), we will not, in fact, see
anyone else's tuple, because they'll all be forced to go through the
first phase and find our pre-existing, never-deleted tuple, so we
can't see any new tuple from them. And, because they super delete
before releasing their token, they'll definitely have super deleted
when we're woken up, so we can't see any old/existing tuple either. We
have our tuple inserted this whole time - ergo, we do, in fact, "win"
reliably.

The fly in the ointment is regular inserters, perhaps, but we've
agreed that they're not too important here, and even when that happens
we're in "deadlock land", not "livelock land", which is obviously a
nicer place to live.

Does that make sense?
--
Peter Geoghegan