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

On 02/19/2015 08:16 PM, Peter Geoghegan wrote:
> On Thu, Feb 19, 2015 at 5:21 AM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>> Hmm. I haven't looked at your latest patch, but I don't think you need to
>> pre-check for this to work. To recap, the situation is that two backends
>> have already inserted the heap tuple, and then see that the other backend's
>> tuple conflicts. To avoid a livelock, it's enough that one backend
>> super-deletes its own tuple first, before waiting for the other to complete,
>> while the other other backend waits without super-deleting. No?
>
> 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.

- Heikki