Global SQL Query

The pgxc_stat_activity function and view are used to implement global SQL query.

1. Log in as the OS user omm to the host where a CN is deployed. Run source ${BIGDATA_HOME}/mppdb/.mppdbgs_profile to start environment variables.
2. Run the following command to connect to the database:

   gsql -d postgres -p 8000

3. Run the following commands to create the pgxc_stat_activity function:

   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';

4. Run the following command to create the pgxc_stat_activity view:

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

5. Run the following SQL statement to query global session information:

   SELECT * FROM PUBLIC.pgxc_stat_activity order by coorname;