Diagnose running SQL queries with the pg_stat_activity system view - AnalyticDB

pg_stat_activity is a system view in AnalyticDB for PostgreSQL that displays queries currently running on an instance. Each row shows a server process and details its associated user session and query. Use this view to analyze running SQL tasks and troubleshoot related issues.

Requirement

Only superusers or the owner of a process can view its details in the pg_stat_activity view.

Field descriptions

Field	Type	Description
datid	oid	Database OID.
datname	name	Database name.
procpid	integer	Backend process ID. Note Supported in v4.3 only.
pid	integer	Backend process ID. Note Supported in v6.0 only.
sess_id	integer	Session ID.
usesysid	oid	User OID.
usename	name	User name.
current_query	text	Current query. By default, the text is truncated to 1024 characters. Use the `track_activity_query_size` parameter to display more. Note Supported in v4.3 only.
query	text	The text of the most recent query. For an `active` state, it shows the currently executing query. For other states, it displays the last query executed. By default, the text is truncated to 1024 characters. Use the `track_activity_query_size` parameter to display more. Note Supported in v6.0 only.
waiting	boolean	`True` if the backend is waiting for a lock; otherwise, `false`.
query_start	datetime	The time when the currently active query started. If the `state` is not `active`, this field shows the start time of the previous query.
backend_start	datetime	The time when the current backend process started.
backend_xid	xid	The current transaction ID of the backend process.
backend_xmin	xid	The backend's xmin horizon.
client_addr	inet	The IP address of the client. A null value indicates a connection via a local Unix socket or that it is an internal process, such as autovacuum.
client_port	integer	The TCP port number for communication between the client and the backend. If a Unix socket is used, the value is -1.
client_hostname	text	Client hostname, reported by a reverse DNS lookup of `client_addr`.
application_name	text	Application name.
xact_start	timestamptz	Start time of the current transaction. It is NULL if there is no active transaction. For the first transaction in a session, this value matches `query_start`.
waiting_reason	text	Waiting reason, such as waiting for a lock or for data replication between nodes.
state	text	The current state of the backend. Possible values are: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, and disabled. Note Supported in v6.0 only.
state_change	timestampz	The time when the `state` was last changed. Note Supported in v6.0 only.
rsgid	oid	Resource group OID.
rsgname	text	Resource group name.
rsgqueueduration	interval	For a queued query, the total time it has spent in the queue.

View connection information

Run the following SQL statement to view the current connected users and their client machines.

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)

View SQL query information

To get information about SQL queries executed by the current user:

Version 6.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)

Version 4.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)

To get information about currently running SQL queries:

Version 6.0:

SELECT datname,usename,query
   FROM pg_stat_activity
   WHERE state != 'idle' ;

Version 4.3:

SELECT datname,usename,current_query
   FROM pg_stat_activity
   WHERE current_query != '<IDLE>' ;

Find long-running queries

To find long-running SQL queries:

Version 6.0:

select current_timestamp - query_start as runtime, datname, usename, query
    from pg_stat_activity
    where state != 'idle'
    order by 1 desc;

Version 4.3:

select current_timestamp - query_start as runtime, datname, usename, current_query
    from pg_stat_activity
    where current_query != '<IDLE>'
    order by 1 desc;

The following is a sample output:

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)

In this example, the first query is long-running and remains active after 34 seconds.

Diagnose and fix abnormal SQL queries

If an SQL query runs for a long time without returning results, check whether it is still running or has been blocked.

Version 6.0:

SELECT datname,usename,query
   FROM pg_stat_activity
   WHERE waiting;

Version 4.3:

SELECT datname,usename,current_query
   FROM pg_stat_activity
   WHERE waiting;

Note that this only identifies queries blocked by locks, not those waiting for other reasons like I/O. If this query returns any rows, it confirms a lock is the cause. Proceed to the next step to get details about the blocking and blocked processes.

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 previous query's result identifies the waiting_query and the locking_query, along with their process IDs (w_pid and l_pid). You can now resolve the lock by canceling one of these processes using its PID.
To gracefully cancel a running query:

SELECT pg_cancel_backend(pid)

This command is effective only on sessions actively executing a query, not on idle ones. The cancellation process allows for graceful cleanup and transaction rollback, which may take some time.
To clean up idle sessions or terminate active queries:

SELECT pg_terminate_backend(pid);

The user's connection will be dropped. Avoid using it on a PID that is actively running a query.