Обсуждение: wiki on monitoring locks has queries that don't seem to work
So I'm running 8.4 and go to this page:http://wiki.postgresql.org/wiki/Lock_Monitoring I have a query that is definitely blocked by another query. I run the first or third queries, the ones that explicitly say that they're for <9.2 and neither produces any output. The third query gives me a list of 182 rows, only a few of which are actually locked in any meaningful ways. Now if I run this query: select relname,pg_locks.* from pg_class,pg_locks where relfilenode=relation and not granted; I get the one row for the update / insert / delete that is getting blocked. I could swear that the original query: select bl.pid as blocked_pid, a.usename as blocked_user, kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement 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; worked once upon a time, but no longer. If anyone has a newer query that works on <9.2 they could pass along that would be great. -- To understand recursion, one must first understand recursion.
On Mon, Nov 25, 2013 at 11:57 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
So I'm running 8.4 and go to this
page:http://wiki.postgresql.org/wiki/Lock_Monitoring
I have a query that is definitely blocked by another query. I run the
first or third queries, the ones that explicitly say that they're for
<9.2 and neither produces any output. The third query gives me a list
of 182 rows, only a few of which are actually locked in any meaningful
ways.
Those queries only find row-level locks, not object-level locks (as indicated). I suspect that you are blocking on object-level locks. Maybe you will have better luck with http://wiki.postgresql.org/wiki/Lock_dependency_information.
Now if I run this query:
select relname,pg_locks.* from pg_class,pg_locks where
relfilenode=relation and not granted;
I get the one row for the update / insert / delete that is getting blocked.
Can you show us that row?
Cheers,
Jeff
On Mon, Nov 25, 2013 at 1:47 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > On Mon, Nov 25, 2013 at 11:57 AM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> So I'm running 8.4 and go to this >> page:http://wiki.postgresql.org/wiki/Lock_Monitoring >> >> I have a query that is definitely blocked by another query. I run the >> first or third queries, the ones that explicitly say that they're for >> <9.2 and neither produces any output. The third query gives me a list >> of 182 rows, only a few of which are actually locked in any meaningful >> ways. > > > Those queries only find row-level locks, not object-level locks (as > indicated). I suspect that you are blocking on object-level locks. Maybe > you will have better luck with > http://wiki.postgresql.org/wiki/Lock_dependency_information. > >> >> >> Now if I run this query: >> >> select relname,pg_locks.* from pg_class,pg_locks where >> relfilenode=relation and not granted; >> >> I get the one row for the update / insert / delete that is getting >> blocked. > > > Can you show us that row? > > > Cheers, > > Jeff well it's gone now. it was a delete on sl_log_1 in slony. I'll wait for it to happen again and can repost it. meanwhile I'll try the link you posted. Thanks. -- To understand recursion, one must first understand recursion.