Use the view (pg_stat_activity) to identify and monitor connections where the client has been in the waiting status for an extended period.
Example
The example below shows connections where the client has been in the waiting status for at least 60 minutes.
However, when considering continued compatibility of applications, do not reference system catalogs directly in the following SQL statements.
postgres=# select * from pg_stat_activity where state='idle in transaction' and current_timestamp > cast(query_start + interval '60 minutes' as timestamp); -[ RECORD 1 ]----+------------------------------ datid | 16384 datname | db01 pid | 16875 usesysid | 10 usename | symfo application_name | apl01 client_addr | 192.33.44.15 client_hostname | client_port | 51793 backend_start | 2013-05-31 17:40:24.161826+09 xact_start | 2013-05-31 17:40:27.636134+09 query_start | 2013-05-31 17:40:27.636134+09 state_change | 2013-05-31 17:40:27.636402+09 waiting | f state | idle in transaction query | begin;
See
Refer to "Notes on Application Compatibility" in the Application Development Guide for information on maintaining application compatibility.
Refer to "The Statistics Collector" under "Server Administration" in the PostgreSQL Documentation for information on pg_stat_activity.
