postgres=# begin isolation level repeatable read ;
BEGIN
postgres=# select * from t;
 id 
----
  1
(1 row)

postgres=# create index CONCURRENTLY t_idx on t(id);

postgres=# select pid, locktype, virtualxid, granted, relation::regclass, mode
 from pg_locks
 where pid <> pg_backend_pid() order by pid;
 pid  |  locktype  | virtualxid | granted | relation |           mode           
------+------------+------------+---------+----------+--------------------------
 4185 | relation   |            | t       | t        | AccessShareLock
  4185 | virtualxid | 3/177      | t       |          | ExclusiveLock 
 6794 | relation   |            | t       | t_idx    | RowExclusiveLock
 6794 | virtualxid | 4/255      | t       |          | ExclusiveLock
  6794 | virtualxid | 3/177      | f       |          | ShareLock 
 6794 | relation   |            | t       | t        | ShareUpdateExclusiveLock
(6 rows)

postgres=# WITH RECURSIVE l AS (
postgres(#   SELECT pid, locktype, mode, granted,
postgres(#  ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj
postgres(#   FROM pg_locks
postgres(# ), pairs AS (
postgres(#   SELECT w.pid waiter, l.pid locker, l.obj, l.mode
postgres(#   FROM l w
postgres(#   JOIN l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype=w.locktype AND NOT l.pid=w.pid AND l.granted
postgres(#   WHERE NOT w.granted
postgres(# ), tree AS (
postgres(#   SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pids
postgres(#   FROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l
postgres(#   UNION ALL
postgres(#   SELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()
postgres(#   FROM tree JOIN pairs w ON tree.pid=w.locker AND NOT w.waiter = ANY ( all_pids )
postgres(# )
postgres-# SELECT (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,
postgres-#        replace(a.state, 'idle in transaction', 'idletx') state,
postgres-#        (clock_timestamp() - state_change)::interval(3) AS change_age,
postgres-#        a.datname,tree.pid,a.usename,a.client_addr,lvl,
postgres-#        (SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,
postgres-#        repeat(' .', lvl)||' '||left(regexp_replace(query, '\s+', ' ', 'g'),100) query
postgres-# FROM tree
postgres-# JOIN pg_stat_activity a USING (pid)
postgres-# ORDER BY path;
    ts_age    | state  |  change_age  | datname  | pid  | usename  | client_addr | lvl | blocked |                    query                     
--------------+--------+--------------+----------+------+----------+-------------+-----+---------+----------------------------------------------
 00:03:49.046 | idletx | 00:03:45.447 | postgres | 4185 | postgres |             |   0 |       1 |  select * from t;
 00:03:18.285 | active | 00:03:18.285 | postgres | 6794 | postgres |             |   1 |       0 |  . create index CONCURRENTLY t_idx on t(id);
(2 rows)