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 a superuser you will have full access to monitoring information.

How to do it…

1. You can 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_type IS NOT NULL
AND wait_event_type NOT IN ('Activity', 'Client');

2. 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...