PostgreSQL系统表 pg_stat_activity

发布时间：2020-12-13 17:20:49 所属栏目：百科来源：网络整理

postgres=# d+ pg_stat_activity 
                        View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers | Storage  | Description 
------------------+--------------------------+-----------+----------+-------------
 datid            | oid                      |           | plain    | 
 datname          | name                     |           | plain    | 
 pid              | integer                  |           | plain    | 
 usesysid         | oid                      |           | plain    | 
 usename          | name                     |           | plain    | 
 application_name | text                     |           | extended | 
 client_addr      | inet                     |           | main     | 
 client_hostname  | text                     |           | extended | 
 client_port      | integer                  |           | plain    | 
 backend_start    | timestamp with time zone |           | plain    | 
 xact_start       | timestamp with time zone |           | plain    | 
 query_start      | timestamp with time zone |           | plain    | 
 state_change     | timestamp with time zone |           | plain    | 
 waiting          | boolean                  |           | plain    | 
 state            | text                     |           | extended | 
 query            | text                     |           | extended | 
View definition:
 SELECT s.datid,d.datname,s.pid,s.usesysid,u.rolname AS usename,s.application_name,s.client_addr,s.client_hostname,s.client_port,s.backend_start,s.xact_start,s.query_start,s.state_change,s.waiting,s.state,s.query
   FROM pg_database d,pg_stat_get_activity(NULL::integer) s(datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port),pg_authid u
  WHERE s.datid = d.oid AND s.usesysid = u.oid;

可以看出，pg_stat_activity是一个系统视图。

1. 官方解释：

One row per server process,showing information related to the current activity of that process,such as state and current query.

每一行都表示一个系统进程，显示与当前会话的活动进程的一些信息，比如当前回话的状态和查询等。

2. 详细信息。

Column	Type	Description
`datid`	`oid`	OID of the database this backend is connected to
`datname`	`name`	Name of the database this backend is connected to
`pid`	`integer`	Process ID of this backend
`usesysid`	`oid`	OID of the user logged into this backend
`usename`	`name`	Name of the user logged into this backend
`application_name`	`text`	Name of the application that is connected to this backend
`client_addr`	`inet`	IP address of the client connected to this backend. If this field is null,itindicates(表明) either that the client is connected via a Unixsocket(插座) on the server machine or that this is aninternal(内部的) process such as autovacuum.
`client_hostname`	`text`	Host name of the connected client,as reported by a reverse(相反) DNS lookup(查找) of `client_addr`. This field will only be non-null for IP connections,and only whenlog_hostname is enabled.
`client_port`	`integer`	TCP port number that the client is using for communication with this backend,or`-1` if a Unixsocket(插座) is used
`backend_start`	`timestamp(时间戳) with time zone`	Time when this process was started,i.e.,when the client connected to the server
`xact_start`	`timestamp with time zone`	Time when this process' current transaction(交易) was started,or null if no transaction is active. If the current query is the first of its transaction,this column is equal to the `query_start` column.
`query_start`	`timestamp with time zone`	Time when the currently active query was started,or if `state` is not `active`,when the last query was started
`state_change`	`timestamp(时间戳) with time zone`	Time when the `state` was last changed
`waiting`	`boolean`	True if this backend is currently waiting on a lock
`state`	`text`	Current overall state of this backend. Possible values are: `active`: The backend isexecuting(实行) a query. `idle`: The backend is waiting for a new client command. `idle in transaction`: The backend is in atransaction(交易),but is not currentlyexecuting(实行) a query. `idlein transaction (aborted)`: This state is similar to`idle in transaction`,except one of thestatements(声明) in the transaction caused an error. `fastpath function call`: The backend is executing a fast-path function. `disabled`: This state is reported iftrack_activities is disabled in this backend.
`query`	`text`	Text of this backend's most recent query. If`state` is`active` this field shows the currently executing query. In all other states,it shows the last query that was executed.

3. 举例说明（讲解waiting，state，query的意义）

> active：表示当前用户正在执行查询等操作。

postgres=# select * from pg_stat_activity ;
 datid | datname  | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start        
  |         state_change          | waiting | state  |              query               
-------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------------
--+-------------------------------+---------+--------+----------------------------------
 12860 | postgres | 2534 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 08:15:42.099642+08 | 2015-03-24 09:15:41.756177+08 | 2015-03-24 09:15:41.756177+0
8 | 2015-03-24 09:15:41.756181+08 | f       | active | select * from pg_stat_activity ;
(1 row)

当前用户waiting=f，stat=active，query=select * from pg_stat_activity.

> idle：表示当前用户空闲。

用另一个客户端再登入一个用户先。

postgres=# select * from pg_stat_activity ;
 datid | datname  | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start        
  |         state_change          | waiting | state  |              query               
-------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------------
--+-------------------------------+---------+--------+----------------------------------
 12860 | postgres | 2534 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 08:15:42.099642+08 | 2015-03-24 09:19:55.516083+08 | 2015-03-24 09:19:55.516083+0
8 | 2015-03-24 09:19:55.516087+08 | f       | active | select * from pg_stat_activity ;
 12860 | postgres | 5084 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 09:19:39.404898+08 |                               |                             
  | 2015-03-24 09:19:39.407193+08 | f       | idle   | 
(2 rows)

可以看到，第二个postgres 的stat=idle,表示的是空闲状态，等待命令的输入。

>idle in transaction:表示当前用户在事务中。

postgres=# begin ;
BEGIN
postgres=# select * from book;
 id | name |   tag    
----+------+----------
  1 | java | aa,bb,cc
  2 | C++  | dd,ee
(2 rows)

另一个客户端启动，查询：

postgres=# select * from pg_stat_activity ;
 datid | datname  | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start        
  |         state_change          | waiting |        state        |              query               
-------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------------
--+-------------------------------+---------+---------------------+----------------------------------
 12860 | postgres | 5500 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 10:22:21.344358+08 | 2015-03-24 10:23:44.615123+08 | 2015-03-24 10:23:49.750407+0
8 | 2015-03-24 10:23:49.76975+08  | f       | idle in transaction | select * from book;
 12860 | postgres | 5510 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 10:23:53.79722+08  | 2015-03-24 10:24:02.64689+08  | 2015-03-24 10:24:02.64689+08
  | 2015-03-24 10:24:02.646894+08 | f       | active              | select * from pg_stat_activity ;
(2 rows)

可以看到，一个postgres用户的stat=idle in transcation,表示在事务中。

> idle in transaction (aborted)：表示当前用户在事务中，但是已经发生错误。

postgres=# begin ;
BEGIN
postgres=# select * from book;
 id | name |   tag    
----+------+----------
  1 | java | aa,ee
(2 rows)

postgres=# s;
ERROR:  syntax error at or near "s"
LINE 1: s;
        ^

另一个客户端启动，查询：

postgres=# select * from pg_stat_activity ;
 datid | datname  | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start        
  |         state_change          | waiting |             state             |              query               
-------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------------
--+-------------------------------+---------+-------------------------------+----------------------------------
 12860 | postgres | 5500 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 10:22:21.344358+08 |                               | 2015-03-24 10:28:55.763925+0
8 | 2015-03-24 10:28:55.764049+08 | f       | idle in transaction (aborted) | s;
 12860 | postgres | 5510 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 10:23:53.79722+08  | 2015-03-24 10:28:58.587982+08 | 2015-03-24 10:28:58.587982+0
8 | 2015-03-24 10:28:58.588028+08 | f       | active                        | select * from pg_stat_activity ;
(2 rows)

可以看到，一个postgres用户的stat=idle in transcation（aborted）,表示在事务中发生错误了。

一个进程等待的例子：

一个客户端在事务中进行增加字段：

postgres=# begin ;
BEGIN
postgres=# alter table book add  column addr character varying;
ALTER TABLE
postgres=#

注意还没有提交；

另一个客户端来查询这个表的数据：

postgres=# select * from book;

发现查询语句等在那里，是因为在进行增加字段操作的时候，会有表锁，锁没有释放之前其他进程无法访问该表。

查看进程状态：

postgres=# select * from pg_stat_activity ;
 datid | datname  | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start        
  |         state_change          | waiting |        state        |                        query                         
-------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------------
--+-------------------------------+---------+---------------------+------------------------------------------------------
 12860 | postgres | 5500 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 10:22:21.344358+08 | 2015-03-24 10:55:29.238829+08 | 2015-03-24 10:55:34.670946+0
8 | 2015-03-24 10:55:34.816089+08 | f       | idle in transaction | alter table book add  column addr character varying;
 12860 | postgres | 5510 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 10:23:53.79722+08  | 2015-03-24 10:55:46.919415+08 | 2015-03-24 10:55:46.919415+0
8 | 2015-03-24 10:55:46.919419+08 | t       | active              | select * from book;
 12860 | postgres | 5764 |       10 | postgres | psql             |             |                 |          -1 | 2015-03-24 11:02:37.09896+08  | 2015-03-24 11:02:52.233883+08 | 2015-03-24 11:02:52.233883+0
8 | 2015-03-24 11:02:52.233886+08 | f       | active              | select * from pg_stat_activity ;
(3 rows)

可以看到第二个进程的waiting=t。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!