Re: list blocking queries

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Scot Kreienkamp <SKreien(at)la-z-boy(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: list blocking queries
Date: 2012-01-31 02:49:29
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

Scot Kreienkamp <SKreien(at)la-z-boy(dot)com> writes:
> My apologies for the slightly novice post on this, but I'm a bit stumped. I have this query that I found on the net and adapted a little to find the queries that were blocking:

> "select as \"Blocked PID\", a.usename as \"Blocked User\", as \"Blocking PID\", ka.usename as \"Blocking User\", to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as \"Age\" from pg_catalog.pg_locks bl join pg_catalog.pg_stat_activity a on = a.procpid join pg_catalog.pg_locks kl join pg_catalog.pg_stat_activity ka on = ka.procpid on bl.transactionid = kl.transactionid and != where not bl.granted;"

Hm, that would only have worked for rather small values of "work",
because it's matching pg_locks entries on the basis of the transactionid
field, which means it will only detect conflicts for locks on
transaction IDs. There are a lot of other types of locks. You need
something more like

join ... on bl.locktype = kl.locktype
and bl.database is not distinct from kl.database
and bl.relation is not distinct from kl.relation
and is not distinct from
and bl.tuple is not distinct from kl.tuple
and bl.virtualxid is not distinct from kl.virtualxid
and bl.transactionid is not distinct from kl.transactionid
and bl.classid is not distinct from kl.classid
and bl.objid is not distinct from kl.objid
and bl.objsubid is not distinct from kl.objsubid
and !=

Since most of these fields will be nulls in any specific rows, you have
to use "is not distinct from" not just "=". Tedious, I know.

The WHERE clause seems a few bricks shy of a load as well; you need

where kl.granted and not bl.granted

if you don't want it to claim that fellow blockees are blocking each
other. (In some cases that would actually be a fair statement, but
I don't think it's possible to tell from pg_locks who's queued behind
whom in the wait-list for a lock, so it's probably best not to try
to show those relationships.)

regards, tom lane

In response to


Browse pgsql-general by date

  From Date Subject
Next Message Josh Berkus 2012-01-31 03:07:52 Re: [GENERAL] Why extract( ... from timestamp ) is not immutable?
Previous Message Tom Lane 2012-01-31 01:41:17 Re: [GENERAL] Why extract( ... from timestamp ) is not immutable?