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

I can happily report that my system has gone through the night without any
problems. Thanks a lot for helping me.

--- Adam Kavan
--- akavan(at)cox(dot)net