全局SQL查询

通过pgxc_stat_activity函数和视图实现全局SQL查询。

1. 执行如下命令连接数据库。

   gsql -d postgres -p 8000

2. 执行如下命令创建pgxc_stat_activity函数。

   DROP FUNCTION PUBLIC.pgxc_stat_activity() cascade;
   CREATE OR REPLACE FUNCTION PUBLIC.pgxc_stat_activity
   (
   OUT coorname text,
   OUT datname text,
   OUT usename text,
   OUT pid bigint,
   OUT application_name text,
   OUT client_addr inet,
   OUT backend_start timestamptz,
   OUT xact_start timestamptz,
   OUT query_start timestamptz,
   OUT state_change timestamptz,
   OUT waiting boolean,
   OUT enqueue text,
   OUT state text,
   OUT query_id bigint,
   OUT query text
   )
   RETURNS setof RECORD
   AS $$
   DECLARE
   row_data pg_stat_activity%rowtype;
   coor_name record;
   fet_active text;
   fetch_coor text;
   BEGIN
   --Get all the node names
   fetch_coor := 'SELECT node_name FROM pg_catalog.pgxc_node WHERE node_type=''C''';
   FOR coor_name IN EXECUTE(fetch_coor) LOOP
   coorname := coor_name.node_name;
   fet_active := 'EXECUTE DIRECT ON (' || coorname || ') ''SELECT * FROM pg_catalog.pg_stat_activity WHERE pid != pg_catalog.pg_backend_pid() and application_name not in (SELECT node_name FROM pg_catalog.pgxc_node WHERE node_type=''''C''''); ''';
   FOR row_data IN EXECUTE(fet_active) LOOP
   datname := row_data.datname;
   pid := row_data.pid;
   usename := row_data.usename;
   application_name := row_data.application_name;
   client_addr := row_data.client_addr;
   backend_start := row_data.backend_start;
   xact_start := row_data.xact_start;
   query_start := row_data.query_start;
   state_change := row_data.state_change;
   waiting := row_data.waiting;
   enqueue := row_data.enqueue;
   state := row_data.state;
   query_id := row_data.query_id;
   query := row_data.query;
   RETURN NEXT;
   END LOOP;
   END LOOP;
   return;
   END; $$
   LANGUAGE 'plpgsql';

3. 执行如下命令创建pgxc_stat_activity视图。

   CREATE VIEW PUBLIC.pgxc_stat_activity AS SELECT * FROM PUBLIC.pgxc_stat_activity();

4. 执行如下sql语句查询全局会话信息。

   SELECT * FROM PUBLIC.pgxc_stat_activity order by coorname;