Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 4916F633220
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  2 Mar 2009 17:24:42 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 57269-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  2 Mar 2009 17:24:39 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.152])
	by mail.postgresql.org (Postfix) with ESMTP id 5900A63316C
	for <pgsql-performance@postgresql.org>;
	Mon,  2 Mar 2009 17:24:39 -0400 (AST)
Received: by fg-out-1718.google.com with SMTP id e12so854928fga.23
	for <pgsql-performance@postgresql.org>;
	Mon, 02 Mar 2009 13:24:37 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:sender:date:from:to:cc
	:subject:message-id:references:mime-version:content-type
	:content-disposition:in-reply-to:user-agent;
	bh=cxKj5iZOfTeI3tC4QRNBey6R3pKAbVneDk0tdou5ylU=;
	b=N5KwoX7HpnFTBQum34Gh9vMXEatA0JC2YT8jGa5vhfo+mMdyi1GCz+UeGRSw5W5yZq
	HOm7k8FjeNWjIe1lvpsDg0ngh7HJlPeybekXuJARLZ/CtBRjazVE8glmWX/EKjgCrhUm
	OpNbVZL4qdzF+DBBoH/7hekTMbQE6KT8YoiCQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=sender:date:from:to:cc:subject:message-id:references:mime-version
	:content-type:content-disposition:in-reply-to:user-agent;
	b=nwB5HBjJjtNXK+m78O/L54Lle+FQvZjbxYV4FaRHgmyZMIKwwb46z1sb9DvdUlqigW
	LCdMJ5XNARwfIl92pq4j3kHyAKPpg1ONTg3etoe4cPDaGnRuhQ5IUj7E8CRi2O5DCFRN
	FU/3s7a2Vq7LSHbj+5PvNk+IxdHSUeZsnzbyw=
Received: by 10.86.76.16 with SMTP id y16mr5512950fga.19.1236029077486;
	Mon, 02 Mar 2009 13:24:37 -0800 (PST)
Received: from timac.local
 (86-41-217-20-dynamic.b-ras1.lmk.limerick.eircom.net
	[86.41.217.20])
	by mx.google.com with ESMTPS id x6sm4843528gvf.24.2009.03.02.13.24.35
	(version=SSLv3 cipher=RC4-MD5); Mon, 02 Mar 2009 13:24:36 -0800 (PST)
Date: Mon, 2 Mar 2009 21:24:33 +0000
From: Tim Bunce <Tim.Bunce@pobox.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Brian Cox <brian.cox@ca.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: "slow" queries
Message-ID: <20090302212433.GX8731@timac.local>
References: <49AC23FF.9070103@ca.com> <22227.1236022171@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <22227.1236022171@sss.pgh.pa.us>
User-Agent: Mutt/1.5.17 (2007-11-01)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200903/19
X-Sequence-Number: 32941

On Mon, Mar 02, 2009 at 02:29:31PM -0500, Tom Lane wrote:
> Brian Cox <brian.cox@ca.com> writes:
> > select locktype,database,relation,virtualxid,virtualtransaction,pid,mode 
> > from pg_locks order by mode;
> 
> If you hadn't left out the "granted" column we could be more sure,
> but what it looks like to me is the DROP (pid 13842) is stuck behind
> the <IDLE> transaction (pid 13833).  In particular these two rows of
> pg_locks look like a possible conflict:
> 
> >   relation      | 26472437 | 26472508 |            | 15/69749 
> > | 13842 | AccessExclusiveLock
> 
> >   relation      | 26472437 | 26472508 |            | 11/131 
> > | 13833 | AccessShareLock

Would it be possible to write a stored procedure that would read
pg_locks, and other relevant tables, and list what's blocking what
in a simplified form?

Tim.