GP - Report all current lock waits
SQL to list out all locks that are waiting.
select bl.pid as blocked_pid,
a.usename as blocked_user,
ka.current_query as blocking_statement,
now() - ka.query_start as blocking_duration,
kl.pid as blocking_pid,
ka.usename as blocking_user,
a.current_query as blocked_statement,
now() - a.query_start as blocked_duration
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.pid != kl.pid
where not bl.granted and
kl.pid != pg_backend_pid()
select bl.pid as blocked_pid,
a.usename as blocked_user,
ka.current_query as blocking_statement,
now() - ka.query_start as blocking_duration,
kl.pid as blocking_pid,
ka.usename as blocking_user,
a.current_query as blocked_statement,
now() - a.query_start as blocked_duration
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.pid != kl.pid
where not bl.granted and
kl.pid != pg_backend_pid()
Comments
Post a Comment