Postgres Hanging on Inserts
My application involves inserting new records into a table and altering a record in another table in a postgres database version 7.3.3 several times a second . Each instance of my application inserts into the same table and changes a different row of the updating table.
My problem is that on occasion (from several minuets to several hours) all of my inserts will stop and will ps -ef tells me that they are waiting for something. I have waited several hours and they have never stopped waiting.
I looked into pg_locks and they are all waiting to get an exclusive lock on the same relation. Is there anyway for me to tell what this relation is? Does anyone know what it could be and how I can fix this problem?
--- Adam Kavan
--- American Amuesments
--- akavan@cox.net
--- 402-499-5145
Adam Kavan <akavan@cox.net> writes:
I looked into pg_locks and they are all waiting to get an exclusive
lock on the same relation. Is there anyway for me to tell what this
relation is?
To decipher the OIDs in pg_locks, join against pg_class.oid, or just do
select relname from pg_class where oid = nnnn;
Does anyone know what it could be and how I can fix this
problem?
Look for the process that already has a lock on the same relation, and
find out what it's waiting for.
regards, tom lane
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <akavan@cox.net>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, July 30, 2003 9:25 AM
Subject: Re: [GENERAL] Postgres Hanging on Inserts
Adam Kavan <akavan@cox.net> writes:
I looked into pg_locks and they are all waiting to get an exclusive
lock on the same relation. Is there anyway for me to tell what this
relation is?To decipher the OIDs in pg_locks, join against pg_class.oid, or just do
select relname from pg_class where oid = nnnn;Does anyone know what it could be and how I can fix this
problem?Look for the process that already has a lock on the same relation, and
find out what it's waiting for.regards, tom lane
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. Those are the only locks either pid doesn't
have so I suspect that is what is causing the deadlock. Is there something
I've done wrong? Both pids are just doing simple inserts.
data=# select * from pg_locks where relation = 3731653 order by granted;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+-----------------+---------
3731653 | 16976 | | 10091 | ShareLock | f
3731653 | 16976 | | 10077 | ShareLock | f
3731653 | 16976 | | 10178 | ShareLock | f
3731653 | 16976 | | 10116 | ShareLock | f
3731653 | 16976 | | 10108 | ShareLock | f
3731653 | 16976 | | 10076 | ShareLock | f
3731653 | 16976 | | 10079 | ShareLock | f
3731653 | 16976 | | 10110 | ShareLock | f
3731653 | 16976 | | 10023 | ExclusiveLock | f
3731653 | 16976 | | 10177 | ShareLock | f
3731653 | 16976 | | 10208 | ShareLock | f
3731653 | 16976 | | 10166 | ShareLock | f
3731653 | 16976 | | 10142 | ShareLock | f
3731653 | 16976 | | 10160 | ShareLock | f
3731653 | 16976 | | 10214 | ShareLock | f
3731653 | 16976 | | 10226 | ShareLock | f
3731653 | 16976 | | 10031 | ShareLock | f
3731653 | 16976 | | 10237 | ShareLock | f
3731653 | 16976 | | 10075 | ShareLock | f
3731653 | 16976 | | 10109 | ShareLock | f
3731653 | 16976 | | 10207 | ShareLock | f
3731653 | 16976 | | 10190 | ShareLock | f
3731653 | 16976 | | 10041 | ShareLock | f
3731653 | 16976 | | 10130 | ShareLock | f
3731653 | 16976 | | 10043 | ShareLock | f
3731653 | 16976 | | 10026 | ShareLock | f
3731653 | 16976 | | 10074 | ShareLock | f
3731653 | 16976 | | 10092 | ShareLock | f
3731653 | 16976 | | 10158 | ShareLock | f
3731653 | 16976 | | 10024 | ExclusiveLock | f
3731653 | 16976 | | 10141 | ShareLock | f
3731653 | 16976 | | 10189 | ShareLock | f
3731653 | 16976 | | 10238 | ShareLock | f
3731653 | 16976 | | 10027 | ShareLock | f
3731653 | 16976 | | 10078 | ShareLock | f
3731653 | 16976 | | 10025 | ExclusiveLock | f
3731653 | 16976 | | 10159 | ShareLock | f
3731653 | 16976 | | 10225 | ShareLock | f
3731653 | 16976 | | 9951 | ShareLock | f
3731653 | 16976 | | 10029 | ShareLock | f
3731653 | 16976 | | 10196 | ShareLock | f
3731653 | 16976 | | 10028 | ShareLock | f
3731653 | 16976 | | 10128 | ShareLock | f
3731653 | 16976 | | 9951 | AccessShareLock | t
3731653 | 16976 | | 10024 | ExclusiveLock | t
3731653 | 16976 | | 10025 | ExclusiveLock | t
3731653 | 16976 | | 9951 | ShareLock | t
3731653 | 16976 | | 10023 | ShareLock | t
(48 rows)
Show quoted text
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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... you should change the hash index to a btree index and the
problem will "go away"; you will also probably see a performance improvement
if there's concurrent insertion and access. BTrees are way more developed
than hashes.
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"The ability to monopolize a planet is insignificant
next to the power of the source"
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
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