Transaction lock waits

Locking performance issues will often be evident by an excess of clients that are waiting for a lock to be granted. If you join two pg_locks entries together with a matching pair of pg_stat_activity ones, it's possible to find out various information about both the locker process that currently holds the lock, and the locked one stuck waiting for it:

SELECT
  locked.pid AS locked_pid,
  locker.pid AS locker_pid,
  locked_act.usename AS locked_user,
  locker_act.usename AS locker_user,
  locked.virtualtransaction,
  locked.transactionid,
  locked.locktype
FROM
  pg_locks locked,
  pg_locks locker,
  pg_stat_activity locked_act,
  pg_stat_activity locker_act
WHERE
  locker.granted=true AND
  locked.granted=false AND
  locked.pid=locked_act.procpid AND
  locker.pid=locker_act.procpid AND
  (locked.virtualtransaction=locker.virtualtransaction OR
   locked.transactionid=locker.transactionid);

This variation looks for and provides additional information about...