Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id F0C0CB5DBC4
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  3 Nov 2011 15:45:43 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 74314-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  3 Nov 2011 18:45:38 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id B9A02B5DBBD
	for <pgsql-performance@postgresql.org>;
	Thu,  3 Nov 2011 15:45:37 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id pA3IjamC006735;
	Thu, 3 Nov 2011 14:45:36 -0400 (EDT)
To: Claudio Freire <klaussfreire@gmail.com>
cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Blocking excessively in FOR UPDATE 
In-reply-to: 
 <CAGTBQpY7zd9h+xv4Gvh0+-G1UgiWg1zd7zUsXPDFvQoGa4JScA@mail.gmail.com>
References: 
 <CAGTBQpY7zd9h+xv4Gvh0+-G1UgiWg1zd7zUsXPDFvQoGa4JScA@mail.gmail.com>
Comments: In-reply-to Claudio Freire <klaussfreire@gmail.com>
	message dated "Thu, 03 Nov 2011 14:51:24 -0300"
Date: Thu, 03 Nov 2011 14:45:36 -0400
Message-ID: <6734.1320345936@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-3.101 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.201
X-Spam-Level: 
X-Archive-Number: 201111/65
X-Sequence-Number: 45576

Claudio Freire <klaussfreire@gmail.com> writes:
> The same query, without FOR UPDATE, takes just 68 milliseconds.
> With the FOR UPDATE, it takes like half a minute or more to finish.

> Now, I understand the for update part may be blocking on some other
> transaction, and it's probably the case.

Yeah, that's what I'd guess.

> But I cannot figure out which transaction it would be. There *are*, in
> fact, connections in <idle in transaction> state, which makes me think
> those would be the culprit. But for the life of me, I cannot make
> sense of the pg_locks view, which shows all locks as granted:

A block on a row would typically show up as one transaction waiting on
another's XID.  Did you capture this *while* the query was blocked?
Also, I'm suspicious that you may be using a view that filters out
the relevant lock types --- that's obviously not a raw display of
pg_locks.

			regards, tom lane