Decoding lock information

The information provided by pg_locks is very basic. In order to understand what it means in more real-world terms, you would want to match its data against several system catalog tables, as well as the activity information for the query. Here's an example showing how to relate this view against the most popular ones to join it against:

SELECT
  locktype,
  virtualtransaction,
  transactionid,
  nspname,
  relname,
  mode,
  granted,
  cast(date_trunc('second',query_start) AS timestamp) AS query_start,
  substr(current_query,1,25) AS query
FROM 
  pg_locks 
    LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
    LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace),
  pg_stat_activity
WHERE
  NOT pg_locks.pid=pg_backend_pid() AND
  pg_locks.pid=pg_stat_activity.procpid;

Note that the way this query discovers table relation names will only produce results for tables in the currently connected database; they...