PostgreSQL 9 Administration Cookbook - Second Edition

Checking which queries are active or blocked

Here, we will show you how to know whether a query is actually running or it is waiting for another query.

Getting ready

Again, log in as a superuser.

How to do it…

Run this query:

SELECT datname, usename, query
       FROM pg_stat_activity
       WHERE waiting = true;

You will get a list of queries that are waiting on other backends. The following query will run on PostgreSQL versions older than 9.2:

SELECT datname, usename, current_query
       FROM pg_stat_activity
       WHERE waiting = true;

How it works…

The pg_stat_activity system view has a Boolean field named waiting. This field indicates that a certain backend is waiting on a system lock.

The preceding query uses it to filter out only those queries that are waiting.

There's ...