Scot Kreienkamp <SKreien@xxxxxxxxxxxx> 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 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 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
