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

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.

Granted, it's unlikely that we'd livelock due to one session always
deleting and then nullifying that by re-inserting in time, but the
theoretical risk seems real. Therefore, I'm not inclined to bother
committing something without a pre-check, particularly since we're not
really interested in fixing unprincipled deadlocks for ordinary
exclusion constraint inserters (useful to know that you also think
that doesn't matter, BTW). Does that make sense?

This is explained within "livelock insurance" new-to-V2.3 comments in
check_exclusion_or_unique_constraint(). (Not that I think that
explanation is easier to follow than this one).

> It might be easier to provoke the livelocks with a GiST opclass that's
> unusually slow. I wrote the attached opclass for the purpose of testing this
> a while ago, but I haven't actually gotten around to do much with it. It's
> called "useless_gist", because it's a GiST opclass for integers, like
> btree_gist, but the penalty and picksplit functions are totally dumb. The
> result is that the tuples are put to the index in pretty much random order,
> and every scan has to scan the whole index. I'm posting it here, in the hope
> that it happens to be useful, but I don't really know if it is.

Thanks. I'll try and use this for testing. Haven't been able to break
exclusion constraints with the jjanes_upsert test suite in a long
time, now.

--
Peter Geoghegan