PostgreSQL 12中如何获取运行查询对应的pg_namespace及模式名？

方法一：通过解析查询文本关联模式 #

如果查询语句中明确包含表名，可通过正则提取表名，再关联pg_class和pg_namespace获取模式名，适合简单查询场景：

SELECT
    sa.pid,
    sa.query,
    n.nspname AS schema_name
FROM
    pg_stat_activity sa
CROSS JOIN LATERAL (
    -- 提取FROM后的表名，可根据实际查询调整正则规则
    SELECT regexp_match(sa.query, 'FROM\s+([^\s.;]+)', 'i') AS table_match
) t
JOIN pg_class c ON c.relname = t.table_match[1]
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE
    sa.state = 'active'
    AND sa.query NOT LIKE '%pg_stat_activity%'
    -- 筛选运行超过5分钟的查询，可按需调整时长
    AND now() - sa.query_start > '5 minutes'::interval;

注意：该方法对带别名、多表连接、含特殊字符表名的查询支持有限，可能出现匹配不准确的情况。

方法二：通过锁关联模式（推荐） #

利用长时间运行查询持有的表锁，直接关联到pg_class和pg_namespace，无需解析查询文本，准确性更高：

SELECT
    sa.pid,
    sa.query,
    n.nspname AS schema_name
FROM
    pg_stat_activity sa
JOIN pg_locks l ON l.pid = sa.pid
JOIN pg_class c ON c.oid = l.relation
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE
    sa.state = 'active'
    AND now() - sa.query_start > '5 minutes'::interval
    -- 匹配查询常见的锁模式，可根据实际情况调整
    AND l.mode IN ('AccessShareLock', 'RowShareLock')
GROUP BY sa.pid, sa.query, n.nspname;

方法三：针对调用函数的查询 #

如果长时间运行的是函数调用，可通过提取函数名关联pg_proc获取模式：

SELECT
    sa.pid,
    sa.query,
    n.nspname AS schema_name
FROM
    pg_stat_activity sa
CROSS JOIN LATERAL (
    -- 提取CALL后的函数名
    SELECT regexp_match(sa.query, 'CALL\s+([^\s(]+)', 'i') AS func_match
) t
JOIN pg_proc p ON p.proname = t.func_match[1]
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE
    sa.state = 'active'
    AND sa.query LIKE '%CALL%'
    AND now() - sa.query_start > '5 minutes'::interval;

内容的提问来源于stack exchange，提问作者LiteWait