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

On Mon, Feb 02, 2015 at 4:48 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
>> I think that the fundamental, unfixable race condition here is the
>> disconnect between index tuple insertion and checking for would-be
>> exclusion violations that exclusion constraints naturally have here,
>> that unique indexes naturally don't have [1] (note that I'm talking
>> only about approach #2 to value locking here; approach #1 isn't in
>> V2.0). I suspect that the feature is not technically feasible to make
>> work correctly with exclusion constraints, end of story. VACUUM
>> interlocking is probably also involved here, but the unfixable race
>> condition seems like our fundamental problem.
>
> It's not a fundamental, unfixable race condition. In [1], I gave you
> three ideas straight off the top of my head on how that could be fixed.

That was different - I tried to make it work by fixing some bugs
there. However, I'm now finding myself up against these new bugs. I
think that the underlying cause is the lack of any real locking
(unlike with the B-Tree AM) in *both* cases, but I don't even know
that for sure. The error messages you see are quite odd - why should a
btree_gist-based exclusion constraint cause a violation when
non-conflicting values are inserted? There is some other race
condition here. This wasn't a livelock (or a deadlock), which is what
your comments in early January apply to. I think that this has
something to do with VACUUM interlocking. But with the B-Tree AM
(which we're handling differently, by re-using infrastructure used for
deferred unique constraints), things work quite well. The patch stands
up to Jeff's vigorous stress-tests.

I'm not fundamentally in disagreement with you about any of this. All
I'm saying is that we should cut scope today. We're not precluding
picking up an IGNORE feature that does support exclusion constraints
in the future. Why should we insist upon having that in the first cut?
It's both significantly harder, and significantly less useful to
users, and so cutting that makes perfect sense AFAICT. As I've said
many times, exclusion constraint support was only ever going to be
useful to the IGNORE variant (I've tested exclusion constraints by
contriving a case to make them do UPSERTs, but this is only for the
benefit of the stress-test).

Thanks
--
Peter Geoghegan