pg_locks "at-a-glance" view

I was trying to create a more "at-a-glance" view of the pg_locks table.
I included the SQL I came up with (after talking to Merlin) at the
bottom of this message.

The idea is to show any queries that are waiting on a lock, and the
query that currently holds the lock on which those queries are waiting.

Is my logic correct? Does anyone have any suggestions?

I couldn't find a generally accepted way to do this, although I'm sure
someone must have done something like this before.

Also, I had to define a function "lock_conflict()" (also included)
because I couldn't find a function to determine if two lock modes
conflict. The function is somewhat thrown together so it may have a few
problems; I just included it so people can run the example view.

Regards,
Jeff Davis

CREATE OR REPLACE VIEW query_lock_wait AS
SELECT
l1.pid AS pid,
a1.current_query AS query,
l2.pid AS waiting_on_pid,
a2.current_query AS waiting_on_query
FROM
pg_locks l1,
pg_locks l2,
pg_stat_activity a1,
pg_stat_activity a2
WHERE
l1.pid = a1.procpid AND
l2.pid = a2.procpid AND
NOT l1.granted AND
l2.granted AND
l1.locktype = l2.locktype AND
l1.pid <> l2.pid AND
(
(l1.locktype, l1.database, l1.relation, l1.page, l1.tuple,
l1.virtualxid, l1.transactionid, l1.classid, l1.objid, l1.objsubid)
IS NOT DISTINCT FROM
(l2.locktype, l2.database, l2.relation, l2.page, l2.tuple,
l2.virtualxid, l2.transactionid, l2.classid, l2.objid, l2.objsubid)
) AND
lock_conflict(l1.mode, l2.mode);

CREATE OR REPLACE FUNCTION lock_conflict(TEXT, TEXT)
RETURNS BOOLEAN LANGUAGE plpgsql AS
$$
BEGIN
IF $1 = 'AccessShareLock' THEN
IF $2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'RowShareLock' THEN
IF $2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'RowExclusiveLock' THEN
IF $2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'ShareUpdateExclusiveLock' THEN
IF $2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'ShareLock' THEN
IF $2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'ShareRowExclusiveLock' THEN
IF $2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'ExclusiveLock' THEN
IF $2 = 'RowShareLock' OR
$2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RAISE EXCEPTION 'Invalid Lock Mode: %', $1;
END IF;
END;
$$;