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
Field | Type | Description |
---|---|---|
datid | oid | The object identifier (OID) of the database. |
datname | name | The name of the database. |
procpid | integer | The ID of the backend process.
Note This field is supported only for AnalyticDB for PostgreSQL V4.3.
|
pid | integer | The ID of the backend process.
Note This field is supported only for AnalyticDB for PostgreSQL V6.0.
|
sess_id | integer | The ID of the session. |
usesysid | oid | The OID of the user. |
usename | name | The name of the user. |
current_query | text | The 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.
|
query | text | The 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.
|
waiting | boolean | Indicates whether the current query is waiting on a lock. Valid values: True and False. |
query_start | datetime | The 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_start | datetime | The time when the current backend process was started. |
backend_xid | xid | The transaction ID of the current backend process. |
backend_xmin | xid | The xmin horizon of the backend. |
client_addr | inet | The 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_port | integer | The 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_hostname | text | The hostname of the client. This field can be reported by a reverse DNS lookup of
client_addr .
|
application_name | text | The name of the application on the client. |
xact_start | timestamptz | The 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_reason | text | The reason why the backend is waiting. The backend is waiting on a lock or on replication of data among nodes. |
state | text | The 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_change | timestampz | The time when the state was last changed.
Note This field is supported only for AnalyticDB for PostgreSQL V6.0.
|
rsgid | oid | The OID of the resource group. |
rsgname | text | The name of the resource group. |
rsgqueueduration | interval | The amount of time for which the query has been queued. |
View connection information
You can execute the following SQL statement to view the connected users and their clients:
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;
datname | usename | client_addr | client_port
---------+----------+---------------+-------------
postgres | joe | xx.xx.xx.xx | 60621
postgres | gpmon | xx.xx.xx.xx | 60312
(9 rows)
Query the SQL execution information
You can execute the following SQL statements to view current queries executed by users who connect to the database:
AnalyticDB for PostgreSQL V6.0:
SELECT datname,usename,query FROM pg_stat_activity ;
datname | usename | query
----------+---------+--------------------------------------------------------------
postgres | postgres | SELECT datname,usename,query FROM pg_stat_activity ;
postgres | joe |
(2 rows)
AnalyticDB for PostgreSQL V4.3:
SELECT datname,usename,current_query FROM pg_stat_activity ;
datname | usename | current_query
----------+---------+--------------------------------------------------------------
postgres | postgres | SELECT datname,usename,current_query FROM pg_stat_activity ;
postgres | joe | <IDLE>
(2 rows)
You can execute the following SQL statements to view active queries:
AnalyticDB for PostgreSQL V6.0:
SELECT datname,usename,query
FROM pg_stat_activity
WHERE state != 'idle' ;
AnalyticDB for PostgreSQL V4.3:
SELECT datname,usename,current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>' ;
View long-running queries
You can execute the following SQL statements to view long-running queries:
AnalyticDB for PostgreSQL V6.0:
select current_timestamp - query_start as runtime, datname, usename, query
from pg_stat_activity
where state != 'idle'
order by 1 desc;
AnalyticDB for PostgreSQL V4.3:
select current_timestamp - query_start as runtime, datname, usename, current_query
from pg_stat_activity
where current_query != '<IDLE>'
order by 1 desc;
Sample query results:
runtime | datname | usename | current_query
----------------+---------------+---------+------------------------------------------------------------------------------
00:00:34.248426 | tpch_1000x_col | postgres | select
: l_returnflag,
: l_linestatus,
: sum(l_quantity) as sum_qty,
: sum(l_extendedprice) as sum_base_price,
: sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
: sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
: avg(l_quantity) as avg_qty,
: avg(l_extendedprice) as avg_price,
: avg(l_discount) as avg_disc,
: count(*) as count_order
: from
: public.lineitem
: where
: l_shipdate <= date '1998-12-01' - interval '93' day
: group by
: l_returnflag,
: l_linestatus
: order by
: l_returnflag,
: l_linestatus;
00:00:00 | postgres | postgres | select
: current_timestamp - query_start as runtime,
: datname,
: usename,
: current_query
: from pg_stat_activity
: where current_query != '<IDLE>'
: order by 1 desc;
(2 rows)
The first returned query executes for a long time. The first query has been executing for 34 seconds and continues to execute.
Diagnose and troubleshoot abnormal SQL queries
If an SQL query has been executing for an extended period of time without returning a result, you must check whether the query has been blocked.
AnalyticDB for PostgreSQL V6.0:
SELECT datname,usename,query
FROM pg_stat_activity
WHERE waiting;
AnalyticDB for PostgreSQL V4.3:
SELECT datname,usename,current_query
FROM pg_stat_activity
WHERE waiting;
The preceding statements can return SQL queries that are blocked only due to lock waits. Queries that are blocked for other reasons are not returned. In most cases, SQL queries are blocked due to lock waits. However, in some other cases, SQL queries are blocked because they are waiting for I/O operations or timers. If the preceding statements have returned query results, specific SQL queries are blocked due to lock waits. You can execute the following statement to view details of these SQL queries:
SELECT
w.query as waiting_query,
w.pid as w_pid,
w.usename as w_user,
l.query as locking_query,
l.pid as l_pid,
l.usename as l_user,
t.schemaname || '.' || t.relname as tablename
from pg_stat_activity w
join pg_locks l1 on w.pid = l1.pid and not l1.granted
join pg_locks l2 on l1.relation = l2.relation and l2.granted
join pg_stat_activity l on l2.pid = l.pid
join pg_stat_user_tables t on l1.relation = t.relid
where w.waiting;
The preceding statement can return the blocked SQL queries and their process IDs. You can review the details of the blocked SQL queries and then cancel or kill the queries to remove the blocks. You can execute the following statement to cancel an active query:
SELECT pg_cancel_backend(pid)
The pg_cancel_backend function can take effect only on active query sessions. The function does not affect idle sessions. In addition, canceling the query may take some time to clear sessions or roll back transactions. You can use pg_terminate_backend to clear idle sessions or terminate queries.
SELECT pg_terminate_backend(pid);
Connections initiated by the specified user are terminated. We recommend that you do not invoke the pg_terminate_backend function on processes identified by pid where active queries exist. To perform the operations mentioned in this topic, you must have superuser permissions.