更新时间:2022-07-04 GMT+08:00

全局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;