DBA: кто скрывается за блокировкой

WITH lm(ld, lr) AS (
  VALUES
    ('AccessShareLock', '{AccessExclusiveLock}'::text[])
  , ('RowShareLock', '{ExclusiveLock,AccessExclusiveLock}'::text[])
  , ('RowExclusiveLock', '{ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])
  , ('ShareUpdateExclusiveLock', '{ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])
  , ('ShareLock', '{RowExclusiveLock,ShareUpdateExclusiveLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])
  , ('ShareRowExclusiveLock', '{RowExclusiveLock,ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])
  , ('ExclusiveLock', '{RowShareLock,RowExclusiveLock,ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])
  , ('AccessExclusiveLock', '{AccessShareLock,RowShareLock,RowExclusiveLock,ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])
)
, locks AS (
  SELECT
    (
      locktype
    , database
    , relation
    , page
    , tuple
    , virtualxid
    , transactionid::text::bigint
    , classid
    , objid
    , objsubid
    ) target
  , *
  FROM
    pg_locks
)
, ld AS (
  SELECT
    *
  FROM
    locks
  WHERE
    NOT granted
)
, lr AS (
  SELECT
    *
  FROM
    locks
  WHERE
    target::text = ANY(ARRAY(
      SELECT DISTINCT
        target::text
      FROM
        ld
    )) AND
    granted
)
, lcx AS (
  SELECT
    lr.target
  , ld.pid ldp
  , ld.mode ldm
  , lr.pid lrp
  , lr.mode lrm
  FROM
    ld
  JOIN
    lr
      ON lr.pid <> ld.pid AND
        lr.target IS NOT DISTINCT FROM ld.target
)
, cfl AS (
SELECT
  lc.locktype "type"
, CASE lc.locktype
    WHEN 'relation' THEN
      ARRAY[relation]
    WHEN 'extend' THEN
      ARRAY[relation]
    WHEN 'page' THEN
      ARRAY[relation, page]
    WHEN 'tuple' THEN
      ARRAY[relation, page, tuple]
    WHEN 'transactionid' THEN
      ARRAY[transactionid::text::oid]
    WHEN 'virtualxid' THEN
      string_to_array(virtualxid::text, '/')::oid[]
    WHEN 'object' THEN
      ARRAY[classid, objid, objsubid]
    WHEN 'userlock' THEN
      ARRAY[classid]
    WHEN 'advisory' THEN
      ARRAY[classid, objid, objsubid]
  END target
, nullif(lc.pid = lcx.ldp, FALSE) as locked
, lc.pid
, regexp_replace(lc.mode, 'Lock$', '') "mode"
, nullif(lc.granted, TRUE) "granted"
, nullif(lc.target IS NOT DISTINCT FROM lcx.target, FALSE) "conflict"
FROM
  lcx
JOIN
  locks lc
    ON lc.pid IN (lcx.ldp, lcx.lrp)
)
SELECT
  cfl.*
, CASE
    WHEN "type" NOT IN ('virtualxid', 'transactionid') THEN target[1]::regclass
  END relname
, cl.relkind
FROM
  cfl
LEFT JOIN LATERAL(
    SELECT
      *
    FROM
      pg_class
    WHERE
      cfl.type = 'relation' AND
      oid = target[1]
    LIMIT 1
  ) cl
    ON TRUE
ORDER BY                                            -- сортируем ...
  locked                                            -- сначала кого блокируют
, pid                                               -- по принадлежности процессу (1 процесс = 1 транзакция)
, CASE "type"                                       -- по приоритету типов блокировок
    WHEN 'virtualxid'    THEN 0
    WHEN 'transactionid' THEN 1
    WHEN 'relation'      THEN 2
    WHEN 'tuple'         THEN 3
    WHEN 'object'        THEN 4
    WHEN 'advisory'      THEN 5
  END
, CASE relkind                                       -- по принадлежности объекта таблице
    WHEN 'r' THEN cl.oid
    WHEN 't' THEN regexp_replace(cl.relname, E'^.*\\D(\\d+)$', E'\\1', '')::oid
    WHEN 'i' THEN (
      SELECT
        indrelid
      FROM
        pg_index
      WHERE
        indexrelid = cl.oid
      LIMIT 1
    )
    WHEN 'S' THEN (
      SELECT
        (
          SELECT
            adrelid
          FROM
            pg_attrdef
          WHERE
            oid = dp.objid
        )
      FROM
        pg_depend dp
      WHERE
        (refclassid, refobjid) = ('pg_class'::regclass, cl.oid) AND
        (deptype, classid) = ('n', 'pg_attrdef'::regclass)
      LIMIT 1
    )
  END
  -- https://postgrespro.ru/docs/postgresql/12/catalog-pg-class
, CASE relkind                                       -- по типу объекта БД
    WHEN 'r' THEN 0 -- relation
    WHEN 'm' THEN 1 -- materialized view
    WHEN 'p' THEN 2 -- partitioned table
    WHEN 'f' THEN 3 -- foreign table
    WHEN 't' THEN 4 -- TOAST
    WHEN 'i' THEN 5 -- index
    WHEN 'I' THEN 6 -- partitioned index
    WHEN 'S' THEN 7 -- sequence
    WHEN 'c' THEN 8 -- composite type
    WHEN 'v' THEN 9 -- view
  END
, CASE                                               -- по типу индекса – PK вперед
    WHEN relkind = 'i' THEN
      NOT (
        SELECT
          indisprimary
        FROM
          pg_index
        WHERE
          indexrelid = cl.oid
        LIMIT 1
      )
  END
, cl.relname                                         -- по имени объекта
, CASE mode                                          -- по приоритету режима блокировки
    WHEN 'AccessExclusive'      THEN 0
    WHEN 'Exclusive'            THEN 1
    WHEN 'ShareRowExclusive'    THEN 2
    WHEN 'Share'                THEN 3
    WHEN 'ShareUpdateExclusive' THEN 4
    WHEN 'RowExclusive'         THEN 5
    WHEN 'RowShare'             THEN 6
    WHEN 'AccessShare'          THEN 7
  END;