Hey everyone,
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;"
To test it I ran this twice simultaneously:
begin work;
lock table traffic in EXCLUSIVE mode;
select pg_sleep(300);
commit work;
PG_stat_activity does show the second lock table query as waiting, but the query I pasted above returns zero results. I have not run this same test on 8.3 as pg_sleep didn't exist then, but I know the query works because I've used it in my 8.3 production environment. Bottom line is I'm not sure if the query to list blocked/blocking PID's doesn't work on 9.1 or if it's the way I'm testing it.
That query is a bit above my head for now as I'm only a part time DBA at my job. Thank you for any insights you can give me.
BTW, the streaming replication and hot standby on 9.1 rocks!!! Thanks to everyone involved for that!!
Scot Kreienkamp
skreien@la-z-boy.com
This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, please note that you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.