pg_stat_activity is a system view that allows you to identify active SQL queries in AnalyticDB for PostgreSQL instances. The pg_stat_activity view shows a server process and its related session and query in each row. You can use pg_stat_activity to analyze and perform diagnostics on active SQL queries and troubleshoot problems.

Precautions

Only superusers and the owners of reporting processes have permissions to use the pg_stat_activity view.

Fields of the pg_stat_activity view

FieldTypeDescription
datidoidThe object identifier (OID) of the database.
datnamenameThe name of the database.
procpidintegerThe ID of the backend process.
Note This field is supported only for AnalyticDB for PostgreSQL V4.3.
pidintegerThe ID of the backend process.
Note This field is supported only for AnalyticDB for PostgreSQL V6.0.
sess_idintegerThe ID of the session.
usesysidoidThe OID of the user.
usenamenameThe name of the user.
current_querytextThe query that is being executed. By default, the query text can contain up to 1,024 characters in length and additional characters are truncated. To display more characters in the query text, you can specify the track_activity_query_size parameter.
Note This field is supported only for AnalyticDB for PostgreSQL V4.3.
querytextThe most recent query. If the state field is set to active, the query being executed is displayed. If the state field is set to a value other than active, the last query is displayed. By default, the query text can contain up to 1,024 characters in length and additional characters are truncated. To display more characters in the query text, you can specify the track_activity_query_size parameter.
Note This field is supported only for AnalyticDB for PostgreSQL V6.0.
waitingbooleanIndicates whether the current query is waiting on a lock. Valid values: True and False.
query_startdatetimeThe time when the active query was started. If the state field is set to a value other than active, the query_start field indicates the time when the last query was started.
backend_startdatetimeThe time when the current backend process was started.
backend_xidxidThe transaction ID of the current backend process.
backend_xminxidThe xmin horizon of the backend.
client_addrinetThe IP address of the client. If client_addr is left empty, the client is connected by using a UNIX socket on the server, or this is an internal process such as auto-vacuum.
client_portintegerThe TCP port that is used for the communications between the client and the backend. A value of -1 indicates that a UNIX socket is used.
client_hostnametextThe hostname of the client. This field can be reported by a reverse DNS lookup of client_addr.
application_nametextThe name of the application on the client.
xact_starttimestamptzThe time when the current transaction was started. If no transactions are active, this field is left empty. If the current query is the first transaction, this field is equivalent to the query_start field.
waiting_reasontextThe reason why the backend is waiting. The backend is waiting on a lock or on replication of data among nodes.
statetextThe current state of the backend. Valid values: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, and disabled.
Note This field is supported only for AnalyticDB for PostgreSQL V6.0.
state_changetimestampzThe time when the state was last changed.
Note This field is supported only for AnalyticDB for PostgreSQL V6.0.
rsgidoidThe OID of the resource group.
rsgnametextThe name of the resource group.
rsgqueuedurationintervalThe amount of time for which the query has been queued.

Examples

You can execute the following statement to find the queries that were initiated by the odps_in user within the last 30 minutes but have not been completed:

select * from pg_stat_activity where xact_start < now() - interval '30 min' and waiting='f' and usename='odps_in';