Database Reference
In-Depth Information
Watching queries from ps
If you want, you can also make the queries being run show up in process titles, by setting
the following:
update_process_titleĀ = on
Although
ps
or
top
output is not the best place for watching the database queries; it may
make sense in some circumstances.
See also
The page in PostgreSQL's online documentation, which covers related settings, is available at
the following URL:
http://www.postgresql.org/docs/9.0/interactive/runtime-config-
statistics.html
Are they active or blocked?
Here we show how to find out if a query is actually running, or is it waiting for some other query.
Getting ready
Again, log in as a superuser.
How to do it...
Run the following query:
SELECT datname,usename,current_query
FROM pg_stat_activity
WHERE waiting = true;
You get a list of queries which are waiting on other backends.
How it works...
The system view
pg_stat_activity
has a boolean field
waiting
, which selects
pg_
terminate_backend
(procpid) from
pg_stat_activity
,
where
current_query
=
<IDLE>
in transaction, and
current_timestamp
-
query_start
>
'1
min';
indicates
that a certain backend is waiting on a system lock.
The preceding query uses it to filter out only queries which are waiting.