Updated on 2022-09-15 GMT+08:00

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;