PostgreSQL系统表 pg_stat_activity

来源：互联网发布：七上生物行知天下答案编辑：程序博客网时间：2024/05/18 03:51

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. 详细信息。

ColumnTypeDescriptiondatidoidOID of the database this backend is connected todatnamenameName of the database this backend is connected topidintegerProcess ID of this backendusesysidoidOID of the user logged into this backendusenamenameName of the user logged into this backendapplication_nametextName of the application that is connected to this backendclient_addrinetIP 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_hostnametextHost 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_portintegerTCP port number that the client is using for communication with this backend, or-1 if a Unixsocket(插座) is usedbackend_starttimestamp(时间戳) with time zoneTime when this process was started, i.e., when the client connected to the serverxact_starttimestamp with time zoneTime 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_starttimestamp with time zoneTime when the currently active query was started, or if state is not active, when the last query was startedstate_changetimestamp(时间戳) with time zoneTime when the state was last changedwaitingbooleanTrue if this backend is currently waiting on a lockstatetextCurrent 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.
idle in transaction (aborted): This state is similar toidle 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.

querytextText of this backend's most recent query. Ifstate isactive 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+08 | 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+08 | 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 ;BEGINpostgres=# 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+08 | 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 ;BEGINpostgres=# select * from book; id | name |   tag    ----+------+----------  1 | java | aa,bb,cc  2 | C++  | dd,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+08 | 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+08 | 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 ;BEGINpostgres=# alter table book add  column addr character varying;ALTER TABLEpostgres=#

注意还没有提交；

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

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+08 | 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+08 | 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+08 | 2015-03-24 11:02:52.233886+08 | f       | active              | select * from pg_stat_activity ;(3 rows)

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

0 0