Knowing who is blocking a query

Once you have found out that a query is blocked, you need to know who or what is blocking them.

Getting ready

If you are logged in as superuser you will have full access tomonitoring information

How to do it...

If you are on PostgreSQL 9.6 or newer, just write the following query:

SELECT datname
, usename
, wait_event_type
, wait_event
, pg_blocking_pids(pid) AS blocked_by
, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

You will get an output like the following:

-[ RECORD 1 ]---+-----------------
datname         | postgres
usename         | gianni
wait_event_type | Lock
wait_event      | relation
blocked_by      | {18142}
query           | select * from t;

This is in fact the query described in the previous recipe, with the addition of the column called blocked_by. Recall that the PID is the unique identifier assigned by the operating system to each session; for more details, see Chapter 4, Server Control. Here the PID is used by the pg_blocking_pids(pid...