On Mon, May 11, 2015 at 9:47 AM, Andres Freund <and...@anarazel.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
