Re: Blocking excessively in FOR UPDATE

On Thu, Nov 3, 2011 at 3:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Claudio Freire <klaussfreire(at)gmail(dot)com> writes:
>> 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?

Yes

> 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.

It's pgadmin, which I usually use to monitor pg_stats_activity and
pg_locks in a "pretty" view.
pg_locks does not show the query, only the pid, so it's harder to spot.

Next time I find it blocking, I will check pg_locks directly and post
the output.

I did that once, and they were all granted. I didn't correlate with
other XIDs since I thought the "granted" column meant it wasn't
waiting. Is that wrong?