pg_stat_activity is a system view in AnalyticDB for PostgreSQL that shows one row per server process, with details about the session and its current or most recent query. Use it to identify active queries, diagnose slow sessions, and investigate blocked or long-running transactions.
Access control
Only superusers and the owners of reporting processes have permissions to use the pg_stat_activity view.
Common diagnostic queries
The following examples cover the most frequent use cases. See Fields for the full column reference.
Find queries initiated by a specific user with long-running transactions
Returns queries initiated by the odps_in user within the last 30 minutes that have not been completed:
select * from pg_stat_activity where xact_start < now() - interval '30 min' and waiting='f' and usename='odps_in';Find queries waiting on a lock
Returns sessions currently blocked waiting on a lock:
SELECT sess_id, usename, query, query_start
FROM pg_stat_activity
WHERE waiting = 't';Find idle sessions holding an open transaction
Returns sessions that are not actively running a query but have an open transaction:
SELECT sess_id, usename, xact_start, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';The state field is available in AnalyticDB for PostgreSQL V6.0 only. In V4.3, use the waiting field to identify blocked sessions.
Fields
| Field | Type | Description |
|---|---|---|
datid | oid | Object identifier (OID) of the database. |
datname | name | Name of the database. |
procpid | integer | ID of the backend process. Note Supported in V4.3 only. |
pid | integer | ID of the backend process. Note Supported in V6.0 only. |
sess_id | integer | ID of the session. |
usesysid | oid | OID of the user. |
usename | name | Name of the user. |
current_query | text | Query currently being executed. Truncated at 1,024 characters by default. To increase this limit, set track_activity_query_size. Note Supported in V4.3 only. |
query | text | Most recent query. If state is active, this is the query being executed. If state is any other value, this is the last query that ran. Truncated at 1,024 characters by default. To increase this limit, set track_activity_query_size. Note Supported in V6.0 only. |
waiting | boolean | Whether the query is waiting on a lock. Valid values: true and false. |
waiting_reason | text | Reason the backend is waiting. Possible reasons: waiting on a lock, or waiting for data replication among nodes. |
query_start | datetime | Time when the active query started. If state is not active, this is the time when the last query started. |
backend_start | datetime | Time when the backend process started. |
backend_xid | xid | Transaction ID of the current backend process. |
backend_xmin | xid | xmin horizon of the backend. |
client_addr | inet | IP address of the client. Empty if the client connected via a UNIX socket, or if the row represents an internal process such as autovacuum. |
client_port | integer | TCP port used for communication between the client and the backend. -1 indicates a UNIX socket connection. |
client_hostname | text | Hostname of the client, resolved by a reverse DNS lookup of client_addr. |
application_name | text | Name of the application on the client. |
xact_start | timestamptz | Time when the current transaction started. Empty if no transaction is active. Equal to query_start when the current query is the first transaction. |
state | text | Current state of the backend. Valid values: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, and disabled. Note Supported in V6.0 only. |
state_change | timestampz | Time when state last changed. Note Supported in V6.0 only. |
rsgid | oid | OID of the resource group. |
rsgname | text | Name of the resource group. |
rsgqueueduration | interval | Length of time the query has been queued in the resource group. |