PostgreSQL 10 High Performance - Third Edition

Table lock waits

Clients waiting to acquire a lock on an entire table is something you should aim to avoid. The following will show you when this is happening:

    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,
      relname
    FROM
      pg_locks locked
        LEFT OUTER JOIN pg_class ON (locked.relation = pg_class.oid),
      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.relation=locker.relation;  

Output from this query looks similar to the previous transactionid examples: ...