locks in postgres #nc

01-just-locks.sql

select
  lock.locktype,
  lock.relation::regclass,
  lock.mode,
  lock.transactionid as tid,
  lock.virtualtransaction as vtid,
  lock.pid,
  lock.granted
from pg_catalog.pg_locks lock
  left join pg_catalog.pg_database db
    on db.oid = lock.database
where (db.datname = current_database() or db.datname is null)
  and not lock.pid = pg_backend_pid()
order by lock.pid;

02-activity.sql

select query, state, waiting, pid
from pg_catalog.pg_stat_activity
where datname = current_database()
  and not (state = 'idle' or pid = pg_backend_pid());

03-lock-monitor.sql

select
  coalesce(
    blocking_lock.relation::regclass::text,
    blocking_lock.locktype)
  as locked_item,
  now() - blocked.query_start as waiting_duration,
  blocked.pid as blocked_pid,
  blocked.query as blocked_query,
  blocked_lock.mode as blocked_mode,
  blocking.pid as blocking_pid,
  blocking.query as blocking_query,
  blocking_lock.mode as blocking_mode
from pg_catalog.pg_locks blocked_lock
  join pg_stat_activity blocked
    on blocked.pid = blocked_lock.pid
  join pg_catalog.pg_locks blocking_lock
    on blocking_lock.pid != blocked_lock.pid
    and (blocking_lock.transactionid = blocked_lock.transactionid
      or blocking_lock.relation = blocked_lock.relation
      and blocking_lock.locktype = blocked_lock.locktype)
  join pg_stat_activity blocking
    on blocking.pid = blocking_lock.pid
    and blocking.datid = blocked.datid
where not blocked_lock.granted
  and blocking.datname = current_database();

04-create-view-lock-monitor.sql

create view lock_monitor as (
  select
    coalesce(
      blocking_lock.relation::regclass::text,
      blocking_lock.locktype)
    as locked_item,
    now() - blocked.query_start as waiting_duration,
    blocked.pid as blocked_pid,
    blocked.query as blocked_query,
    blocked_lock.mode as blocked_mode,
    blocking.pid as blocking_pid,
    blocking.query as blocking_query,
    blocking_lock.mode as blocking_mode
  from pg_catalog.pg_locks blocked_lock
    join pg_stat_activity blocked
      on blocked.pid = blocked_lock.pid
    join pg_catalog.pg_locks blocking_lock
      on blocking_lock.pid != blocked_lock.pid
      and (blocking_lock.transactionid = blocked_lock.transactionid
        or blocking_lock.relation = blocked_lock.relation
        and blocking_lock.locktype = blocked_lock.locktype)
    join pg_stat_activity blocking
      on blocking.pid = blocking_lock.pid
      and blocking.datid = blocked.datid
  where not blocked_lock.granted
    and blocking.datname = current_database()
);