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

On 02/19/2015 10:09 PM, Peter Geoghegan wrote:
> 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.

So, um, are you agreeing that there is no problem? Or did I
misunderstand? If you see a potential issue here, can you explain it as
a simple list of steps, please.

- Heikki