Diagnose running SQL queries with the pg_stat_activity system view - AnalyticDB

Prerequisites

Only superusers or the process owner can query 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	Most recent query text. In `active` state, shows the running query; otherwise, the last executed query. 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	Start time of the current query. If `state` is not `active`, shows the start time of the last query.
backend_start	datetime	Start time of the backend process.
backend_xid	xid	Transaction ID of the backend process.
backend_xmin	xid	The backend's xmin horizon.
client_addr	inet	Client IP address. NULL indicates a local Unix socket connection or an internal process such as autovacuum.
client_port	integer	TCP port used by the client. Returns -1 for Unix socket connections.
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. NULL if no active transaction. Equals `query_start` for the first transaction in a session.
waiting_reason	text	Waiting reason, such as waiting for a lock or for data replication between nodes.
state	text	Backend state. Possible 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 of the last `state` change. 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 spent in the queue.

View connection information

Query connected users and their client addresses:

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

Query SQL statements 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)

Query only active (non-idle) statements:

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;

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)

The first query has been running for 34 seconds and is still active.

Diagnose and resolve blocked queries

If a query runs for a long time without returning results, check whether it is still running or blocked by a lock.

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;

This identifies only lock-blocked queries, not those waiting on I/O. If rows are returned, a lock is the cause. Run the following query to identify 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 result shows the waiting_query and locking_query with their process IDs (w_pid and l_pid). Cancel one of these processes to resolve the lock.
To gracefully cancel a running query:

SELECT pg_cancel_backend(pid)

This works only on sessions actively executing a query. The backend performs a graceful cleanup and transaction rollback, which may take time.
To terminate idle sessions or force-stop active queries:

SELECT pg_terminate_backend(pid);

This drops the connection immediately. Prefer pg_cancel_backend for queries still in progress.