[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Postgres Hanging on Inserts
From:       "Adam Kavan" <akavan () cox ! net>
Date:       2003-07-31 16:14:01
[Download RAW message or body]

> Alvaro Herrera Munoz <alvherre@dcc.uchile.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@cox.net


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html
[prev in list] [next in list] [prev in thread] [next in thread]