Re: BUG #15026: Deadlock using GIST index

On Sat, Feb 3, 2018 at 2:02 AM, Mark Scheffer <pg2401k(at)pinkwin(dot)com> wrote:
> * Exclusion Constraints
> * ---------------------
> ...
> *
> * There is a chance of deadlock, if two backends insert a tuple at the
> * same time, and then perform the scan to check for conflicts. They will
> * find each other's tuple, and both try to wait for each other. The
> * deadlock detector will detect that, and abort one of the transactions.
> * That's fairly harmless, as one of them was bound to abort with a
> * "duplicate key error" anyway, although you get a different error
> * message.
>
> I guess that my example has this deadlock...

> Any chance in changing current Postgresql behavior?

While I think that your complaint is a legitimate one, I'd say that
it's very unlikely to be fixed. Ordinary (non-deferrable) unique
constraints don't have this problem because they pessimistically lock
the first leaf page the value could be on ahead of inserting. In
contrast, exclusion constraints generally optimistically detect any
conflict in a separate pass. That's how you can get concurrent
insertions to wait on each other, rather than having a clear "winner"
at the precise point that insertion occurs.

I can't imagine what it would take to avoid this issue with GiST, but
I'm sure it would be very difficult.

--
Peter Geoghegan