REINDEX/SELECT deadlock?

From: Karl Wright <kwright(at)metacarta(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: REINDEX/SELECT deadlock?
Date: 2008-07-11 16:59:58
Message-ID: 4877918E.7030308@metacarta.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I just noticed what looks like a deadlock situation on postgresql 8.2.4. After more than an hour of running REINDEX, two
processes are each in a "waiting" state and yet have no time used. This is also the first time I've seen this condition after
some 48 hours of continuous load testing.

The two postgresql processes that seem to be stuck on one another are:

1 S postgres 31812 22455 0 80 0 - 274059 semtim 12:13 ? 00:00:00 postgres: metacarta metacarta 127.0.0.1(54297) SELECT
waiting
1 S postgres 32577 22455 0 80 0 - 274063 semtim 12:16 ? 00:00:00 postgres: metacarta metacarta 127.0.0.1(60377)
REINDEX waiting

The rest of the postgresql processes change; here's a snapshot. (Note that process 14349 is active and continues to
intermittently run queries generated by another thread, but should not be blocking anything.) Unfortunately, I cannot tell you
exactly what the deadlocked SELECT query is, but it is likely to be against the same table that the REINDEX command has been
issued for.

1 S postgres 2046 22455 0 80 0 - 274059 - 12:23 ? 00:00:00 postgres: metacarta metacarta 127.0.0.1(33940) idle

1 S postgres 2097 22455 0 80 0 - 274059 - 12:24 ? 00:00:00 postgres: metacarta metacarta 127.0.0.1(36947) idle

0 S root 2380 4263 0 80 0 - 411 stext 12:24 pts/1 00:00:00 grep postgres:
1 R postgres 14349 22455 26 80 0 - 274385 - 11:51 ? 00:08:47 postgres: metacarta metacarta 127.0.0.1(52448) PARSE

1 S postgres 22457 22455 0 80 0 - 273864 - Jul10 ? 00:01:55 postgres: writer process

1 S postgres 22458 22455 0 80 0 - 2380 434161 Jul10 ? 00:00:50 postgres: stats collector process

Does anyone know what may be going on here? Has this been fixed on later versions of postgresql?

Thanks,
Karl

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2008-07-11 17:46:19 Re: 3ware vs Areca
Previous Message Luke Lonergan 2008-07-11 14:59:00 Re: 3ware vs Areca