Re: Deadlock with tsearch2 index ...

"Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> On Tue, 31 May 2005, Tom Lane wrote:
>> I take it from your title that this only happens if there's a tsearch2
>> index on the table? Can you put together a test case?

> I haven't tried this myself, but the client wrote this very quick script
> that apparently recreates it every time:

So it does. What's happening is that since client2 wants to update the
same row client1 already did, client2 blocks for client1 to commit and
release the row lock. Which is reasonable. But for non-concurrent
indexes (ie, gist and rtree), we take out AccessExclusiveLock at the
start of the statement, and release it again at the end of the
statement. So client1 released its index lock, and now client2 is
holding it while blocked on the row, and then client1 needs it back
in order to do its second update.

AFAICS, a near-term fix for this would require either that

(1) we don't release exclusive index locks at end of statement, but
hold them till end of transaction; then client1 would still be holding
the index lock and client2 would be blocked on that, not on the row.
Or

(2) we acquire and release the index lock for each *tuple* rather than
each statement. Then client2 doesn't hold the index lock while it's
waiting for the row lock to clear.

Neither of these cures sounds attractive :-(. I think #1 would probably
do as much to create deadlock cases as to prevent them. #2 would avoid
the deadlock but the performance cost would be high.

The real solution is to upgrade GIST to be concurrent. Oleg and Teodor
have made some noises about that in the past, but nothing's been done
about it that I've heard of.

regards, tom lane