O'Reilly logo

PostgreSQL 9 Administration Cookbook - Second Edition by Simon Riggs, Gianni Ciolli, Hannu Krosing, Gabriele Bartolini

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required