Re: list blocking queries - Mailing list pgsql-general

Tom,

There's something wrong with the query that I've written based on what you gave me.

Here's what I have:

select bl.pid as Blocked_PID,
a.usename as Blocked_User,
kl.pid 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 bl.pid = a.procpid
join pg_catalog.pg_locks kl
join pg_catalog.pg_stat_activity ka on bl.locktype = kl.locktype
        and bl.database is not distinct from kl.database
        and bl.relation is not distinct from kl.relation
        and bl.page is not distinct from kl.page
        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 bl.pid != kl.pid
where kl.granted and not bl.granted

When I run that I get:
ERROR:  syntax error at or near "where"
LINE 20: where kl.granted and not bl.granted

I think I'm missing something in one of the joins.

Thanks!

Scot Kreienkamp
Senior Systems Engineer
skreien@la-z-boy.com


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, January 30, 2012 9:49 PM
To: Scot Kreienkamp
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] list blocking queries

Scot Kreienkamp <SKreien@la-z-boy.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
andadapted a little to find the queries that were blocking: 

> "select bl.pid as \"Blocked PID\", a.usename as \"Blocked User\", kl.pid as \"Blocking PID\", ka.usename as
\"BlockingUser\", to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as \"Age\"  from pg_catalog.pg_locks bl join
pg_catalog.pg_stat_activitya on bl.pid = a.procpid join pg_catalog.pg_locks kl join pg_catalog.pg_stat_activity ka on
kl.pid= ka.procpid on bl.transactionid = kl.transactionid and bl.pid != kl.pid 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 bl.page is not distinct from kl.page
        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 bl.pid != kl.pid

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



This message is intended only for the individual or entity to which it is addressed. It may contain privileged,
confidentialinformation which is exempt from disclosure under applicable laws. If you are not the intended recipient,
pleasenote that you are strictly prohibited from disseminating or distributing this information (other than to the
intendedrecipient) or copying this information. If you have received this communication in error, please notify us
immediatelyby e-mail or by telephone at the above number. Thank you.