在greenplumdb 中，有时候我们需要查询sql 在每个segment instance 上的会话状态信息，用于诊断性能问题等等。
在master上，可以通过查询 pg_stat_activity 视图查出数据库里面的SQL执行会话信息，例如：
testdb1=# select * from pg_stat_activity; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start | waiting_reason -------+---------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+---------------------- ---------+-------------+-------------+------------------+-------------------------------+---------------- 17146 | testdb1 | 10366 | 12 | 10 | gpadmin | <IDLE> | f | 2017-12-04 22:22:03.641018-08 | 2017-12-04 22:21:50.7 3754-08 | | -1 | psql | | 17146 | testdb1 | 11836 | 14 | 10 | gpadmin | <IDLE> | f | 2017-12-05 17:24:21.83859-08 | 2017-12-05 17:10:06.9 82097-08 | | -1 | psql | | 17146 | testdb1 | 12158 | 15 | 10 | gpadmin | <IDLE> in transaction | f | 2017-12-05 17:22:10.787534-08 | 2017-12-05 17:22:06.0 46925-08 | | -1 | psql | 2017-12-05 17:22:10.787534-08 | 17146 | testdb1 | 12496 | 17 | 10 | gpadmin | select * from pg_stat_activity; | f | 2017-12-05 17:39:16.108899-08 | 2017-12-05 17:34:46.3 84621-08 | | -1 | psql | 2017-12-05 17:39:16.108899-08 | (4 rows) testdb1=#
通过如下代码，可以创建一个视图，可以查出包括master和 所有segment的 sql 信息，代码如下：
create function get_segment_id() returns int as $$ declare begin return current_setting('gp_contentid'); end; $$ language plpgsql; create or replace view all_segment_activity as select get_segment_id() as gp_segment_id,* from gp_dist_random('pg_stat_activity')
union all select get_segment_id() as gp_segment_id,* from only pg_stat_activity;
如果要查询某个segment上的sql信息，可以执行如下sql：
testdb1=# select * from all_segment_activity where gp_segment_id =0; gp_segment_id | datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | q uery_start | backend_start | client_addr | client_port | application_name | xact_start | waiting_reason ---------------+-------+---------+---------+---------+----------+---------+------------------------------------------------------------+---------+----------- --------------------+-------------------------------+-----------------+-------------+------------------+-------------------------------+---------------- 0 | 17146 | testdb1 | 12161 | 15 | 10 | gpadmin | <IDLE> in transaction | f | 2017-12-05 17:22:10.799-08 | 2017-12-05 17:22:10.789897-08 | 192.168.189.128 | 39835 | | 2017-12-05 17:22:10.799-08 | 0 | 17146 | testdb1 | 12763 | 14 | 10 | gpadmin | select * from all_segment_activity where gp_segment_id =0; | f | 2017-12-05 17:45:55.995377-08 | 2017-12-05 17:45:44.409823-08 | 192.168.189.128 | 39893 | | 2017-12-05 17:45:55.995377-08 | (2 rows)