根据 PostgreSQL9.2beta1 版的 release note，监控视图 pg_stat_activity 变化蛮大，包括

1. Rename pg_stat_activity.procpid to pid, to match other system tables (Magnus Hagander)
   重命名字段 pg_stat_activity.procpid 为 pid。
2. Create a separate pg_stat_activity column to report state information, e.g. idle (Scott Mead, Magnus Hagander, Greg Smith)
   新增 state 字段，显示语句状态。
3. Rename pg_stat_activity.current_query to query because it is not cleared when the query completes (Magnus Hagander)
   重命名字段 pg_stat_activity.current_query 为 query。

从上面看出，pg_stat_activity 变化挺大，重命名了两个字段，并且新增了 state 字段。

关于 pg_stat_activity 视图字段，详见手册，根据手册说明，query 字段含义有变化：当 state 状态为 active 的会话时，显示当前运行的语句，当 state 为其它状态时，query 显示最近执行的语句；而之前的版本 current_query 只显示当会运行的会话，如果是空闲会话，current_query 值为 。

新增的 state 字段有以下值:

• active: The backend is executing a query. ( 显示当前活跃会话的SQL )
• idle: The backend is waiting for a new client command. ( 当前会话空闲)
• idle in transaction: The backend is in a transaction, but is not currently executing a query. 事务没有提交或回滚时.
• idle in transaction (aborted): This state is similar to idle in transaction, except one of
  the statements in the transaction caused an error. ( 当事务中的语句出错时.)
• fastpath function call: The backend is executing a fast-path function.
  这个还不太理解，以后查到相关资料再来补充。
• disabled: This state is reported if track_activities is disabled in this backend. ( 当 track_activities 设置为 off 时 ).

构造 state 值为 idle 场景

1.1 session A

1
2
3
4

[postgres@redhat6 tf]$ psql francs francs  
psql (9.2beta1)  
Type "help" for help.
francs=>

1.2 session B

1
2
3
4
5

postgres=# select pid, datname,usename,query ,state from pg_stat_activity where pid <> pg_backend_pid();  
pid | datname | usename | query | state  
------+---------+---------+-------+-------  
8151 | francs | francs | | idle  
(1 row)

构造 state 值为 active 场景

1
2
3
4

postgres=# select pid, datname,usename,query ,state from pg_stat_activity;  
pid | datname | usename | query | state  
------+----------+----------+-----------------------------------------------------------------+--------  
8134 | postgres | postgres | select pid, datname,usename,query ,state from pg_stat_activity; | active

备注：state 值为 active 时，表示会话正在执行； 直接查询当前会话时，state 值为 active, 当然可以构造慢查询，然后重新开启窗口验证，这里就不做验证了，如果一个库中 active 状态的语句比较多说明语句需要优化了。

构造 state 值为 idle in transaction 场景

3.1 session A

1
2
3
4
5
6
7
8
9
10

[postgres@redhat6 tf]$ psql francs francs  
psql (9.2beta1)  
Type "help" for help.
francs=> begin;  
BEGIN  
francs=> select count(*) from test_1;  
count  
-------  
10000  
(1 row)

3.2 sesion B

1
2
3
4
5

postgres=# select pid, datname,usename,query ,state from pg_stat_activity where pid <> pg_backend_pid();  
pid | datname | usename | query | state  
------+---------+---------+------------------------------+---------------------  
8165 | francs | francs | select count(*) from test_1; | idle in transaction  
(1 row)

备注：当事务没有结束时，state 字段显示为 idle in transaction， 同时 query 显示的是最近执行的语句。注意这并不是当前执行的语句，而是历史最近执行的语句。

构造 state 值为 idle in transaction (aborted) 场景

4.1 session A

1
2
3
4
5
6
7
8
9

[postgres@redhat6 tf]$ psql francs francs  
psql (9.2beta1)  
Type "help" for help.
francs=> begin;  
BEGIN  
francs=> select count(*) from test_22;  
ERROR: relation "test_22" does not exist  
LINE 1: select count(*) from test_22;
 ^

4.2 session B

1
2
3
4

postgres=# select pid, datname,usename,query ,state from pg_stat_activity where pid <> pg_backend_pid();  
pid | datname | usename | query | state  
------+---------+---------+-------------------------------+-------------------------------  
8181 | francs | francs | select count(*) from test_22; | idle in transaction (aborted)(1 row)

构造 state 值为 disabled 场景

5.1 修改 $PGDATA/postgresql.conf

修改参数配置文件 $PGDATA/postgresql.conf ，设置 track_activities = off，默认为 on。

5.2 修改后 pg_ctl reload -D $PGDATA 重新加载配置文件

1
2

[postgres@redhat6 pg_root]$ pg_ctl reload -D $PGDATA  
server signaled

5.3session A

1
2
3
4

[postgres@redhat6 pg_root]$ psql francs francs  
psql (9.2beta1)  
Type "help" for help.
francs=>

5.4 session B

1
2
3
4
5
6
7
8
9
10
11

postgres=# show track_activities;  
track_activities  
------------------  
off  
(1 row)

postgres=# select pid, datname,usename,query ,state from pg_stat_activity where pid <> pg_backend_pid();  
pid | datname | usename | query | state  
------+---------+---------+-------+----------  
8201 | francs | francs | | disabled  
(1 row)

备注：当参数 track_activities 设置为 off 时，pg_stat_activity.state 值始终为 disabled， 表示不记录 session 信息。

构造有 session 被阻塞的情况

session A 删除一条记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

[postgres@redhat6 pg_root]$ psql francs francs  
psql (9.2beta1)  
Type "help" for help.
francs=> /d test_1  
 Table "francs.test_1"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
name | character varying(32) |  
Indexes:  
 "idx_test_1_id" UNIQUE, btree (id)

francs=> begin;  
BEGIN  

francs=> delete from test_1 where id=1;  
DELETE 1

备注： session 准备删除表的一条记录，注意此时事务未提交。

session B 创建索引

1
2
3
4

[postgres@redhat6 pg_root]$ psql francs francs  
psql (9.2beta1)  
Type "help" for help.
francs=> create index idx_test_1_name on test_1 using btree (name);

备注： session B 准备在表 test_1 上创建一个索引，注意此时会话被 BLOCK，光标下不去。

session C 监控会话

1
2
3
4
5

postgres=# select pid, datname,usename,query ,state ,waiting from pg_stat_activity where pid <> pg_backend_pid();  
pid | datname | usename | query | state | waiting  
------+---------+---------+------------------------------------------------------------+---------------------+---------  
8641 | francs | francs | delete from test_1 where id=1; | idle in transaction | f  
8713 | francs | francs | create index idx_test_1_name on test_1 using btree (name); | active | t(2 rows)

备注：session B 的进程号为 8713 ，虽然它的 state 状态为 active ，但是它处理于等侍状态， waiting 为 t；也就是说 waiting 字段和 state 字段是独立的，如果 state 字段为 active ， 那么会话的 wating 字段可能为 true 也可能为 false。

附

track_activities (boolean)
Enables the collection of information on the currently executing command of each session, along
with the time when that command began execution. This parameter is on by default. Note that even when
enabled, this information is not visible to all users, only to superusers and the user owning the session
being reported on, so it should not represent a security risk. Only superusers can change this setting.

参考

• http://www.postgresql.org/docs/9.2/static/release-9-2.html
• http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE