lockviews.sql

-- SQL comes from here
-- https://momjian.us/main/writings/pgsql/locking.pdf

-- cannot be a temporary view because other sessions must see it
create view lockview as
select
  pid,
  virtualtransaction as vxid,
  locktype as lock_type,
  mode as lock_mode,
  granted,
  case
    when virtualxid is not null and transactionid is not null
    then virtualxid || ' ' || transactionid
    when virtualxid::text is not null
    then virtualxid
    else transactionid::text
  end
    as xid_lock,
  relname,
  page,
  tuple,
  classid,
  objid,
  objsubid
from pg_locks
  left join pg_class
    on pg_locks.relation = pg_class.oid
  -- do not show our view’s locks
where pid != pg_backend_pid()
  -- no need to show self-vxid locks
  and virtualtransaction is distinct from virtualxid
-- granted is ordered earlier
order by 1, 2, 5 desc, 6, 3, 4, 7;

create view lockview1 as
select
  pid,
  vxid,
  lock_type,
  lock_mode,
  granted,
  xid_lock,
  relname
from lockview
-- granted is ordered earlier
order by 1, 2, 5 desc, 6, 3, 4, 7;

create view lockview2 as
select
  pid,
  vxid,
  lock_type,
  page,
  tuple,
  classid,
  objid,
  objsubid
from lockview
-- granted is first
-- add non-display columns to match ordering of lockview
order by 1, 2, granted desc, vxid, xid_lock::text, 3, 4, 5, 6, 7, 8;