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;

```
Postgres has a very rich system catalog exposed via SQL tables.
PG's statistics collector is a subsystem that supports collection and reporting of information about server activity.

Now to figure out the blocking PIDs you can simply query `pg_stat_activity`.

```
select pg_blocking_pids(pid) as blocked_by
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

```

To, get the query corresponding to the blocking PID, you can self-join or use it as a where clause in a subquery.

```
SELECT query
FROM pg_stat_activity
WHERE pid IN (select unnest(pg_blocking_pids(pid)) as blocked_by from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0);

```

Note: Since `pg_blocking_pids(pid)` returns an Integer[], so you need to `unnest` it before you use it in a `WHERE pid IN` clause.

Hunting for slow queries can be tedious sometimes, so have patience. Happy hunting.