Re: Refactoring speculative insertion with unique indexes a little

On Wed, Jul 1, 2015 at 10:45 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> You can construct a theoretical case where lock starvation occurs with
> unique constraint enforcement. I think it helps with nbtree here that
> someone will reliably *not* see a conflict when concurrently
> inserting, because unique index "value locking" exists (by locking the
> first leaf page a value could be on with a buffer lock). But even if
> that wasn't the case, the insert + recheck thing would be safe, just
> as with exclusion constraints...provided you insert to begin with,
> that is.

Of course, the fact that UNIQUE_CHECK_PARTIAL aminsert callers
(including deferred constraint inserters and, currently, speculative
inserters) can rely on this is very useful to UPSERT. It guarantees
progress of one session without messy exclusion constraint style fixes
(for deadlock and livelock avoidance). You cannot talk about
speculative insertion without talking about unprincipled deadlocks
(and maybe livelocks).

If I had to bet where we might find some bugs in the executor parts of
UPSERT, my first guess would be the exclusion constraint edge-case
handling (livelocking stuff). Those are probably relatively benign
bugs, but recent bugs in exclusion constraints (in released branches)
show that they can hide for a long time.
--
Peter Geoghegan