Checking which queries are active or blocked
Here, we will show you how to know whether a query is actually running or waiting for another query.
Getting ready
If you are logged in as superuser you will have full access to monitoring information.
How to do it…
- You can run the following query:
SELECT datname , usename , wait_event_type , wait_event , query FROM pg_stat_activity WHERE wait_event_type IS NOT NULL AND wait_event_type NOT IN ('Activity', 'Client');
- The output will look like the following:
-[ RECORD 1 ]---+----------------- datname | postgres usename | gianni wait_event_type | Lock wait_event | relation query | select * from t;
How it works…
The pg_stat_activity system view includes the wait_event_type and wait_event columns, which are set respectively to the kind of wait and to the kind of object that is blocked.
The preceding query uses the wait_event field to filter out only those queries that are waiting.
There's more…
Further explanation about the preceding...
