Database Reference
In-Depth Information
What are they running?
Here we show how to check what query is currently running.
Getting ready
Make sure that you are logged in as a superuser or as the same database user you want
to check.
Make sure that the parameter
track_activities
=
on
is set.
This can be done either in the
postgresql.conf
file or by the superuser using the following
SQL statement:
SET track_activities = on
How to do it...
To see what all connected users are running now, just run the following:
SELECT datname,usename,current_query FROM pg_stat_activity ;
On systems with a lot of users, you may notice that the majority of backends are running
a weird query
<IDLE>
. This denotes the state, where no query is actually running, and
PostgreSQL is waiting for new commands from the user.
To see information for only active queries, exclude the idle ones by running the following:
SELECT datname,usename,current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>' ;
How it works...
When
track_activities
=
on
is set, then PostgreSQL collects data about all running
queries. Users with sufficient right can then view this data using system view
pg_stat_
activities
.
The view
pg_stat_activities
uses a system function named
pg_stat_get_activity
(procpid int) that you can use directly to watch for activity of a specific backend by supplying the
process ID as an argument. Giving
NULL
as argument returns information for all backends.