Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 92D99A01095
	for <pgsql-admin@postgresql.org>; Wed, 30 Nov 2011 14:07:46 -0400 (AST)
Received: from mail-vw0-f46.google.com ([209.85.212.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <craig_james@emolecules.com>) id 1RVoZL-0003tO-B2
	for pgsql-admin@postgresql.org; Wed, 30 Nov 2011 18:07:46 +0000
Received: by vbbfc26 with SMTP id fc26so629232vbb.19
	for <pgsql-admin@postgresql.org>; Wed, 30 Nov 2011 10:07:30 -0800 (PST)
Received: by 10.182.89.39 with SMTP id bl7mr653088obb.43.1322676450384;
	Wed, 30 Nov 2011 10:07:30 -0800 (PST)
Received: from Craig-Jamess-MacBook-Pro.local
	(wsip-70-167-122-86.sd.sd.cox.net. [70.167.122.86])
	by mx.google.com with ESMTPS id c1sm3978036oba.0.2011.11.30.10.07.27
	(version=SSLv3 cipher=OTHER); Wed, 30 Nov 2011 10:07:28 -0800 (PST)
Message-ID: <4ED6712A.8040603@emolecules.com>
Date: Wed, 30 Nov 2011 10:08:42 -0800
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6;
	rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Deadlock on "select ... for update"?
References: 
 <CACKquuGP-S1xXw4cYr-5VhzkqEC3w9bOkhnXEwqoWKXD3EkU=w@mail.gmail.com>
	<CAOR=d=1Dr8mzoXXr93-ev4pQqK=SsD=eJeHPkDzjTddfg6DTqQ@mail.gmail.com>
In-Reply-To: 
 <CAOR=d=1Dr8mzoXXr93-ev4pQqK=SsD=eJeHPkDzjTddfg6DTqQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201111/161
X-Sequence-Number: 38755

On 11/29/11 10:36 AM, Scott Marlowe wrote:
> On Tue, Nov 29, 2011 at 11:15 AM, Craig James
> <craig_james@emolecules.com>  wrote:
>> Several times recently one of our databases has gotten stuck with the
>> following situation:
>>
>> postgres=# select datname, procpid, usename, current_query from
>> pg_stat_activity where current_query != '<IDLE>';
>>    datname   | procpid | usename  |
>>    current_query
>> ------------+---------+----------+--------------------------------------------------------------------------------------------------------
>>   emolecules |   13503 | customerdb | select tableid from hitlist_table_pool
>> where hitlistid<= 0 for update
>>   emolecules |   32082 | customerdb | select tableid from hitlist_table_pool
>> where hitlistid<= 0 for update
>>   emolecules |   17974 | customerdb | select tableid from hitlist_table_pool
>> where hitlistid<= 0 for update
>>   emolecules |   31299 | customerdb | select tableid from hitlist_table_pool
>> where hitlistid = 0 limit 1 for update
>>   emolecules |   30247 | customerdb | select tableid from hitlist_table_pool
>> where hitlistid = 0 limit 1 for update
>>   postgres   |    1705 | postgres | select datname, procpid, usename,
>> current_query from pg_stat_activity where current_query != '<IDLE>';
>>   emolecules |   28866 | customerdb |<IDLE>  in transaction
>>   emolecules |   21394 | customerdb | select tableid from hitlist_table_pool
>> where hitlistid = 0 limit 1 for update
>>   emolecules |   22237 | customerdb | select tableid from hitlist_table_pool
>> where hitlistid = 0 limit 1 for update
>> (9 rows)
>>
>> It's obvious that they're all waiting ... but for what?  The "<IDLE>"
>> process looks like the culprit, but how do I figure out what it's doing?
>> The next time this happens, what queries can I run to help diagnose what's
>> going on?
>> This is PG 8.4.4 on Ubuntu 10.
> Does this help?
>
> http://wiki.postgresql.org/wiki/Lock_Monitoring

Yes, thanks! That's exactly what I needed.

Craig