查看等待锁的查询和进程：
The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
简化版：
select w1.pid as waiting_pid, w2.usename as waiting_user, w2.query as waiting_statement, b1.pid as blocking_pid, b2.usename as blocking_user, b2.query as blocking_statement from pg_locks w1 join pg_stat_activity w2 on w1.pid=w2.pid join pg_locks b1 on w1.transactionid=b1.transactonid and w1.pid!=b1.pid join pg_stat_activity b2 on b1.pid=b2.pid where not w1.granted;
在上面的SQL中，可能会疑问，为什么要w1.pid != b1.pid，事务ID相同，但PID不同，怎么理解呢：
官方文档说明，https://www.postgresql.org/docs/current/view-pg-locks.html，PID表示持有这把锁的或者等待这把锁的进程，这就解释了 w1.pid!=b1.pid ，表示筛选出持有的锁进程和等待锁的进程。但w1.transactionid=b1.transactonid怎么理解？怎么可能一个事务中对应两个进程？不应该是同一个锁吗？
那么再来理一理，首先无法确定同一个锁。这里的transaction表示作用在改锁上的事务ID，这么理解：
1）进程100，事务ID为111111，正在修改一行数据，持有了RowExclusiveLock，那么在pg_locks中那条记录的transactionid就是111111；
2）而进程101，事务ID为111119，也在修改这行数据，而事务111111没有提交，于是在等待这把锁，那么在pg_locks中记录的transactionid也是111111，而不是111119！
因此可以通过这个事务ID相等，且PID不等于持有事务ID的PID进程，就是正在等待的进程，进而通过PID相等找到pg_stat_activity中的query。而属于事务ID所属的PID则是正在持有锁的进程，对应可以找到正在执行的SQL。
Table 52.74. pg_locks Columns Name Type References Description locktype text Type of the lockable object: relation, extend, page, tuple, transactionid, virtualxid, object, userlock, or advisory database oid pg_database.oid OID of the database in which the lock target exists, or zero if the target is a shared object, or null if the target is a transaction ID relation oid pg_class.oid OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation page integer Page number targeted by the lock within the relation, or null if the target is not a relation page or tuple tuple smallint Tuple number targeted by the lock within the page, or null if the target is not a tuple virtualxid text Virtual ID of the transaction targeted by the lock, or null if the target is not a virtual transaction ID transactionid xid ID of the transaction targeted by the lock, or null if the target is not a transaction ID classid oid pg_class.oid OID of the system catalog containing the lock target, or null if the target is not a general database object objid oid any OID column OID of the lock target within its system catalog, or null if the target is not a general database object objsubid smallint Column number targeted by the lock (the classid and objid refer to the table itself), or zero if the target is some other general database object, or null if the target is not a general database object virtualtransaction text Virtual ID of the transaction that is holding or awaiting this lock pid integer Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction mode text Name of the lock mode held or desired by this process (see Section 13.3.1 and Section 13.2.3) granted boolean True if lock is held, false if lock is awaited fastpath boolean True if lock was taken via fast path, false if taken via main lock table
另外，可以使用来看看当前进程被哪些进程给锁住：pg_blocking_pids
pg_blocking_pids returns an array of the process IDs of the sessions that are blocking the server process with the specified process ID, or an empty array if there is no such server process or it is not blocked. One server process blocks another if it either holds a lock that conflicts with the blocked process's lock request (hard block), or is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue (soft block). When using parallel queries the result always lists client-visible process IDs (that is, pg_backend_pid results) even if the actual lock is held or awaited by a child worker process. As a result of that, there may be duplicated PIDs in the result. Also note that when a prepared transaction holds a conflicting lock, it will be represented by a zero process ID in the result of this function. Frequent calls to this function could have some impact on database performance, because it needs exclusive access to the lock manager's shared state for a short time.