pg_locks "at-a-glance" view

[Jeff Davis <pgsql@xxxxxxxxxxx>]

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;
$$;