PostgreSQL 10 High Performance - Third Edition

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 ...