Once you have found out that a query is being blocked, you need to know who or what is blocking it.
If you are logged in as a superuser, you will have full access to monitoring information.
Perform the following steps:
- 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');
-[ RECORD 1 ]---+----------------- datname | postgres usename | gianni wait_event_type | Lock wait_event | relation blocked_by | {18142} backend_type | client backend query | select * from t;
This is, in fact, the query we described in the previous recipe, with the addition of the blocked_by
column. Recall that the PID is the unique identifier assigned by the operating system to each session; for more details...