heavyweight locks 分析：持久监控

with sa_snapshot as (
  select *
  from pg_stat_activity
  where
    datname = current_database()
    and pid <> pg_backend_pid()
    and state <> 'idle'
)
select
  (extract(epoch from now()) * 1e9)::bigint as epoch_ns,
  waiting.pid as waiting_pid,
  waiting_stm.usename::text as tag_waiting_user,
  waiting_stm.application_name::text as tag_waiting_appname,
  waiting.mode as waiting_mode,
  waiting.locktype as waiting_locktype,
  waiting.relation::regclass::text as tag_waiting_table,
  waiting_stm.query as waiting_query,
  (extract(epoch from (now() - waiting_stm.state_change)) * 1000)::bigint as waiting_ms,
  blocker.pid as blocker_pid,
  blocker_stm.usename::text as tag_blocker_user,
  blocker_stm.application_name::text as tag_blocker_appname,
  blocker.mode as blocker_mode,
  blocker.locktype as blocker_locktype,
  blocker.relation::regclass::text as tag_blocker_table,
  blocker_stm.query as blocker_query,
  (extract(epoch from (now() - blocker_stm.xact_start)) * 1000)::bigint as blocker_tx_ms
from pg_catalog.pg_locks as waiting
join sa_snapshot as waiting_stm on waiting_stm.pid = waiting.pid
join pg_catalog.pg_locks as blocker on
  waiting.pid <> blocker.pid
  and blocker.granted
  and waiting.database = blocker.database
  and (
    waiting.relation  = blocker.relation
    or waiting.transactionid = blocker.transactionid
  )
join sa_snapshot as blocker_stm on blocker_stm.pid = blocker.pid
where not waiting.granted;