Question: I want to track mutual locks in postgres constantly.

I came across [Locks Monitoring](https://wiki.postgresql.org/wiki/Lock_Monitoring) article and tried to run the following query:

```
SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
 JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

```

Unfortunately, it never returns non-empty result set. If I simplify given query to the following form:

```
SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
WHERE NOT bl.granted;

```

then it returns queries which are waiting to acquire a lock. But I cannot manage to change it so that it can return both blocked and blocker queries.

Any ideas?

Answer: 
Since 9.6 this is a lot easier as it introduced the function `pg_blocking_pids()` to find the sessions that are blocking another session.

So you can use something like this:

```
select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

```
This modification of [a\_horse\_with\_no\_name's answer](https://stackoverflow.com/a/43363536/1394393) will give you the last (or current, if it's still actively running) query of the blocking session in addition to just the blocked sessions:

```
SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

```

This helps you understand which operations are interfering with each other (even if the block comes from a previous query), helping you understand the impact of killing one session and figuring out how to prevent blocking in the future.