Message-ID: <4877918E.7030308@metacarta.com>
Date: Fri, 11 Jul 2008 12:59:58 -0400
From: Karl Wright <kwright@metacarta.com>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: REINDEX/SELECT deadlock?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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