Re: [BUGS] BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table

On Mon, May 11, 2015 at 9:47 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2015-05-10 16:01:53 -0400, Tom Lane wrote:
>> The cause of the problem seems to be that the UPDATE performs a HOT update
>> of the new tuple, leaving in this case a dead tuple at (0,2) that is HOT
>> updated by (0,3). When unique_key_recheck() is invoked for (0,2), it
>> believes, correctly, that it has to perform the recheck anyway ... but it
>> tells check_exclusion_constraint that the check is being performed for
>> (0,2). So the index search inside check_exclusion_constraint finds the
>> live tuple at (0,3) and thinks that is a conflict.
>
> Heh, it's curious that this wasn't found up until now. I also wonder if
> this might be related to the spurious violations Peter G. has been
> observing...

I don't think so. Speculative insertion relies on the assumption that
the speculatively inserted tuple isn't MVCC visible to other sessions.
I actually prototyped an implementation that avoided the historic
"unprincipled deadlocks" of exclusion constraints (a known limitation
since they were added), by making *UPDATE* also do a speculative
insertion, and by making even non-UPSERT INSERTs insert speculatively.

This almost worked, but when time came to back out of a speculative
insertion on an UPDATE due to a conflict from a concurrent session,
the implementation couldn't handle it - it was just a mess to try and
figure out how that was supposed to work with heap_update(), and so
that prototype was scrapped.

For the benefit of those not closely involved in the ON CONFLICT
project, I should point out that ON CONFLICT will not accept a
deferred index as an arbiter index.
--
Peter Geoghegan