Knowing who is blocking a query

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

Getting ready

If you are logged in as a superuser, you will have full access to monitoring information.

How to do it…

Perform the following steps:

1. Write the following query:

   SELECT datname, usename, wait_event_type, wait_event, pid, pg_blocking_pids(pid) AS blocked_by, backend_type, query
   FROM pg_stat_activity
   WHERE wait_event_type IS NOT NULL
   AND wait_event_type NOT IN ('Activity', 'Client');
   

2. You will receive the following output:

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

This is the query we described in the previous recipe, with the addition...