Help Center> Data Warehouse Service> Troubleshooting> Database Use> Global SQL Query
View PDF

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;
Parent topic: Database Use