I used to use the following query which is pretty helpful whenever I wanted to find out the queries which are in ‘waiting’ state, but after upgrading to PostgreSQL 9.6, I was unable to run that and use to get the error as shown below.
SELECT pid,state,datname,usename,now() - query_start AS duration ,waiting,query FROM pg_stat_activity WHERE waiting='true';
ERROR: 42703: column "waiting" does not exist
LINE 1: ...,datname,usename,now() - query_start AS duration ,waiting,qu...
LOCATION: errorMissingColumn, parse_relation.c:3090
The reason for the error is that ‘waiting’ column is dropped in PostgreSQL 9.6 and is replaced by two other columns that is:
- wait_event : Name of the wait event for which the backend/query is waiting on. There is a big list of wait events which can be found here.
- wait_event_type: The type of event for which backend/query is waiting for. There are 4 possible values for it which can be found here.
So query will change to following if you want to find out whether it is waiting on something or not.
SELECT pid,state,datname,usename,now() - query_start AS duration ,wait_event,query FROM pg_stat_activity WHERE wait_event IS NOT NULL and state = 'active' ;