Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 70930633B55 for ; Mon, 2 Mar 2009 17:59:51 -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 95276-03 for ; Mon, 2 Mar 2009 17:59:48 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f170.google.com (mail-gx0-f170.google.com [209.85.217.170]) by mail.postgresql.org (Postfix) with ESMTP id 80F5A6330CB for ; Mon, 2 Mar 2009 17:59:48 -0400 (AST) Received: by gxk18 with SMTP id 18so77441gxk.19 for ; Mon, 02 Mar 2009 13:59:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=krRYdf+lmj2Bzjqslcj4MwbWa9I/FJD5v08kct/Lq50=; b=Cfid9ypvPrhG2vJ8a6/LW7Yu/xgy1gbQqesYd+eotdskseVWFQarsP5ztk6uqNF9Jk Cn8rjGIy2kw/trSVNbO7qwE3dTfRB3S2AgOTmULFMAG6EiCa40ThDUxtq4PLAf/L6lxd G88yhEDzbcZZmqdFRPcPqv4Fh5f9y4pRml4Uo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=sIeP1AiHgLGWYO6NCphehL/cclG/PlR+OQeh2HfqvpV7n5qumYbOX/pn+RdxUT7LJ1 uZdrjfmbDZLHnp+mNEKJRqgww01WvMAbte8T54hejM9KGhKW262vnYSY/+z9G7KXZWuN MA+Cgz1SH1CnfYiL1Kye6adRMrLnsb/5a9/Fs= MIME-Version: 1.0 Received: by 10.151.6.16 with SMTP id j16mr11169585ybi.47.1236031187306; Mon, 02 Mar 2009 13:59:47 -0800 (PST) In-Reply-To: <20090302212433.GX8731@timac.local> References: <49AC23FF.9070103@ca.com> <22227.1236022171@sss.pgh.pa.us> <20090302212433.GX8731@timac.local> Date: Mon, 2 Mar 2009 14:59:47 -0700 Message-ID: Subject: Re: "slow" queries From: Scott Marlowe To: Tim Bunce Cc: Tom Lane , Brian Cox , "pgsql-performance@postgresql.org" Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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/20 X-Sequence-Number: 32942 On Mon, Mar 2, 2009 at 2:24 PM, Tim Bunce wrote: > On Mon, Mar 02, 2009 at 02:29:31PM -0500, Tom Lane wrote: >> Brian Cox writes: >> > select locktype,database,relation,virtualxid,virtualtransaction,pid,mo= de >> > 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 transaction (pid 13833). =A0In particular these two rows of >> pg_locks look like a possible conflict: >> >> > =A0 relation =A0 =A0 =A0| 26472437 | 26472508 | =A0 =A0 =A0 =A0 =A0 = =A0| 15/69749 >> > | 13842 | AccessExclusiveLock >> >> > =A0 relation =A0 =A0 =A0| 26472437 | 26472508 | =A0 =A0 =A0 =A0 =A0 = =A0| 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? I'm sure a query could use just connect by from tablefuncs, or WITH under 8.4 and get it.