Активные запросы и блокировки PostgreSQL

# Представление активных запросов
SELECT * FROM pg_stat_activity;

SELECT    query, state, waiting, pid
FROM      pg_stat_activity
WHERE     datname = 'praefecthq_production'
AND NOT   (state = 'idle' OR pid = pg_backend_pid());

# Документация по представлению блокировок
SELECT * FROM pg_locks;

# Список запросов, которые заблокировали друг-друга
SELECT
pg_ca.pid     AS blocked_pid,
pg_ca.query   AS blocked_query,
pg_sa.pid     AS blocking_pid,
pg_sa.query   AS blocking_query
FROM          pg_catalog.pg_locks pg_cl
JOIN          pg_stat_activity pg_ca ON pg_ca.pid = pg_cl.pid
JOIN          pg_catalog.pg_locks pg_lo ON pg_lo.pid != pg_cl.pid
AND           pg_lo.transactionid = pg_cl.transactionid
JOIN          pg_stat_activity pg_sa ON pg_sa.pid = pg_lo.pid
WHERE         NOT pg_cl.granted
AND           pg_sa.datname = 'praefecthq_production';

# Вывести блокирующие запросы из базы
# wait_duration - время выполнения запроса
SELECT pid, query, now() - query_start AS wait_duration
FROM   pg_catalog.pg_stat_activity
WHERE  datname = 'praefecthq_production'
AND    waiting;

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 = 'praefecthq_production' or db.datname is null)
 and not lock.pid = pg_backend_pid()
order by lock.pid;

# Вывести что именно болкировано
SELECT
pg_ca.relation::regclass,
pg_sa.pid     AS blocked_pid,
pg_sa.query   AS blocked_query,
pg_cl.mode    AS blocked_mode,
pg_sa.pid     AS blocking_pid,
pg_sa.query   AS blocking_query,
pg_ca.mode    AS blocking_mode
FROM          pg_catalog.pg_locks pg_cl
JOIN          pg_stat_activity pg_sa
ON            pg_sa.pid = pg_cl.pid
JOIN          pg_catalog.pg_locks pg_ca
ON            pg_cl.pid != pg_ca.pid
AND           pg_ca.relation = pg_cl.relation
AND           pg_ca.locktype = pg_cl.locktype
JOIN          pg_stat_activity pg_sa
ON            pg_sa.pid = pg_ca.pid
WHERE         not pg_cl.granted
AND           pg_sa.datname = 'praefecthq_production';

# Кто кого блокирует

select
 coalesce(bgl.relation::regclass::text, bgl.locktype) as locked_item,
 now() - bda.query_start as waiting_duration,
 bda.pid as blocked_pid,
 bda.query as blocked_query,
 bdl.mode as blocked_mode,
 bga.pid as blocking_pid,
 bga.query as blocking_query,
 bgl.mode as blocking_mode
from pg_catalog.pg_locks bdl
 join pg_stat_activity bda
   on bda.pid = bdl.pid
 join pg_catalog.pg_locks bgl
   on bgl.pid != bdl.pid
   and (bgl.transactionid = bdl.transactionid
     or bgl.relation = bdl.relation and bgl.locktype = bdl.locktype)
 join pg_stat_activity bga
   on bga.pid = bgl.pid
   and bga.datid = bda.datid
where not bdl.granted
 and bga.datname = current_database();
 
 # С ожиданием
  select
   coalesce(bgl.relation::regclass::text, bgl.locktype) as locked_item,
   now() - bda.query_start as waiting_duration,
   bda.pid as blocked_pid,
   bda.query as blocked_query,
   bdl.mode as blocked_mode,
   bga.pid as blocking_pid,
   bga.query as blocking_query,
   bgl.mode as blocking_mode
 from pg_catalog.pg_locks bdl
   join pg_stat_activity bda
     on bda.pid = bdl.pid
   join pg_catalog.pg_locks bgl
     on bgl.pid != bdl.pid
     and (bgl.transactionid = bdl.transactionid
       or bgl.relation = bdl.relation and bgl.locktype = bdl.locktype)
   join pg_stat_activity bga
     on bga.pid = bgl.pid
     and bga.datid = bda.datid
 where not bdl.granted
   and bga.datname = current_database()