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, 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
   blocked_by      | {18142}
   backend_type    | client backend...