Skip site navigation (1) Skip section navigation (2)

Re: Postgres Hanging on Inserts

From: "Adam Kavan" <akavan(at)cox(dot)net>
To: "Alvaro Herrera Munoz" <alvherre(at)dcc(dot)uchile(dot)cl>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Postgres Hanging on Inserts
Date: 2003-07-31 16:14:01
Message-ID: 001101c3577e$c164a440$1400a8c0@aav.local (view raw or flat)
Thread:
Lists: pgsql-general
> 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


In response to

pgsql-general by date

Next:From: Josh BerkusDate: 2003-07-31 16:38:31
Subject: Re: [GENERAL] interesting PHP/MySQL thread
Previous:From: Paul RamseyDate: 2003-07-31 15:15:03
Subject: Re: Upgrading to 7.3.4?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group