Help with pg_locks query - Mailing list pgsql-hackers

I am writing a talk about the lock manager for PG Open and I would like
suggestions on how to improve a query in my talk.  The query creates a
lockinfo_hierarchy view of a recursive query on other views.  The output
shows the locks held and the locks being waited for:

    \! psql -e -c 'SELECT * FROM lockinfo_hierarchy;' | sed 's/^/\t/g'
        SELECT * FROM lockinfo_hierarchy;
         ?column? |  pid  |  vxid  | granted | xid_lock |   lock_type   | relname  | page | tuple
        ----------+-------+--------+---------+----------+---------------+----------+------+-------
         1        | 24860 | 2/3106 | t       | 828      | transactionid |          |      |
         1        | 24864 | 3/42   | t       | 829      | transactionid |          |      |
         1        | 24868 | 4/78   | t       | 830      | transactionid |          |      |
         1        | 24872 | 5/22   | t       | 831      | transactionid |          |      |
         2        | 24864 | 3/42   | f       | 828      | transactionid |          |      |
         3        | 24864 | 3/42   | t       |          | tuple         | lockdemo |    0 |     1
         4        | 24868 | 4/78   | f       |          | tuple         | lockdemo |    0 |     1
         4        | 24872 | 5/22   | f       |          | tuple         | lockdemo |    0 |     1
        (8 rows)

The SQL needed to reproduce this output is attached, and must be run
in your personal database, e.g. postgres.

What this output shows are four transactions holding locks on their own
xids, transaction 3/42 waiting for 828 to complete, and 3/42 holding a
row lock that 4/78 and 5/22 are waiting on.

When there are multiple waiters, one transaction waits on the real xid
and the others sleep waiting to be woken up later.

Is there any better way to show this?  (The first column is just there
for debugging so you can see what part of the query generated the row.)

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
-- cannot be a temporary view because other sessions must see it
DROP VIEW IF EXISTS lockview CASCADE;

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 OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE    -- do not show our view's locks
         pid != pg_backend_pid() AND
    -- no need to show self-vxid locks
    virtualtransaction IS DISTINCT FROM virtualxid
-- granted is ordered earlier
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

DROP VIEW IF EXISTS lockview1 CASCADE;

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;

DROP VIEW IF EXISTS lockview2 CASCADE;

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;

DROP TABLE IF EXISTS lockdemo;
CREATE TABLE lockdemo (col int);
INSERT INTO lockdemo VALUES (1);
-- do two UPDATEs to cause a wait

CREATE VIEW lockinfo_hierarchy AS
    WITH RECURSIVE lockinfo1 AS (
        SELECT '1', pid, vxid, granted, xid_lock, lock_type, relname, page, tuple
        FROM lockview
        WHERE xid_lock IS NOT NULL AND
              relname IS NULL AND
              granted
        UNION ALL
        SELECT '2', lockview.pid, lockview.vxid, lockview.granted, lockview.xid_lock,
            lockview.lock_type, lockview.relname, lockview.page, lockview.tuple
        FROM lockinfo1 JOIN lockview ON (lockinfo1.xid_lock = lockview.xid_lock)
        WHERE lockview.xid_lock IS NOT NULL AND
              lockview.relname IS NULL AND
              NOT lockview.granted AND
              lockinfo1.granted),
    lockinfo2 AS (
        SELECT '3', pid, vxid, granted, xid_lock, lock_type, relname, page, tuple
        FROM lockview
        WHERE lock_type = 'tuple' AND
              granted
        UNION ALL
        SELECT '4', lockview.pid, lockview.vxid, lockview.granted, lockview.xid_lock,
            lockview.lock_type, lockview.relname, lockview.page, lockview.tuple
        FROM lockinfo2 JOIN lockview ON (
            lockinfo2.lock_type = lockview.lock_type AND
            lockinfo2.relname = lockview.relname AND
            lockinfo2.page = lockview.page AND
            lockinfo2.tuple = lockview.tuple)
        WHERE lockview.lock_type = 'tuple' AND
              NOT lockview.granted AND
              lockinfo2.granted
    )
    SELECT * FROM lockinfo1
    UNION ALL
    SELECT * FROM lockinfo2;

-- try several updates
BEGIN WORK;
SELECT ctid, xmin, * FROM lockdemo;
UPDATE lockdemo SET col = 4;
SELECT ctid, xmin, * FROM lockdemo;
SELECT pg_backend_pid();
SELECT txid_current();
\! psql -e -c 'BEGIN WORK; UPDATE lockdemo SET col = 5; SELECT pg_sleep(0.300); COMMIT;' | sed 's/^/\t/g' &
\! psql -e -c 'BEGIN WORK; UPDATE lockdemo SET col = 6; SELECT pg_sleep(0.300); COMMIT;' | sed 's/^/\t/g' &
\! psql -e -c 'BEGIN WORK; UPDATE lockdemo SET col = 7; SELECT pg_sleep(0.300); COMMIT;' | sed 's/^/\t/g' &
SELECT pg_sleep(0.100);
\! psql -e -c 'SELECT * FROM lockview1;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM lockview2;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM lockinfo_hierarchy;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM pg_locks;' | sed 's/^/\t/g'
COMMIT;
\! psql -e -c 'SELECT * FROM lockview1;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM lockview2;' | sed 's/^/\t/g'
SELECT pg_sleep(0.300);

DELETE FROM lockdemo;
INSERT INTO lockdemo VALUES (1);