Re: Postgres Hanging on Inserts

Alvaro Herrera Munoz <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> On Wed, Jul 30, 2003 at 01:03:55PM -0500, Adam Kavan wrote:
>> I have found the problem (I think) below is the list of all the locks
>> pending on the relation. The relation is a hash index on the table that is
>> being INSERT'd rapidly. From what I can see pid 10024 and 10025 both have
>> an ExclusiveLock on the index, and they both are waiting to get an
>> ExclusiveLock on the relation.

> Oh, so this is the problem. Truth is hash indexes in Postgres are known to
> have poor concurrency, though I didn't expect them to be subject to
> deadlocks...

They are known to have internal deadlock problems too. I believe what
Adam has shown us is an internal deadlock in the index. The locks that
are being taken are actually page-level locks, but the pg_locks view
doesn't show the page numbers.

I had thought that such things would trigger a "deadlock detected" error
though --- curious that it seems not to.

> you should change the hash index to a btree index

Agreed. Hash indexes would probably have gotten fixed by now if anyone
could see a reason to expend effort on them, but they seem to be mostly
an academic exercise.

regards, tom lane