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 backend_type = 'client backend' and state='idle in transaction' and current_timestamp > cast(query_start + interval '60 minutes' as timestamp); -[ RECORD 1 ]----+------------------------------ datid | 13003 datname | db01 pid | 4638 leader_pid | usesysid | 10 usename | fsep application_name | apl01 client_addr | 192.33.44.15 client_hostname | client_port | 27500 backend_start | 2022-02-24 09:09:21.730641+09 xact_start | 2022-02-24 09:09:23.858727+09 query_start | 2022-02-24 09:09:23.858727+09 state_change | 2022-02-24 09:09:23.858834+09 wait_event_type | Client wait_event | ClientRead state | idle in transaction backend_xid | backend_xmin | query_id | query | begin; backend_type | client backend
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.
