PostgreSQL中让会话等待表未提交事务及检测相关会话方法

要找出对目标表持有未提交DML相关锁的会话，以及对应的事务开始时间，直接结合pg_locks、pg_stat_activity和pg_class这几个系统视图就能实现，查询语句如下：

SELECT
    sa.pid,
    sa.usename,
    sa.application_name,
    sa.state,
    sa.xact_start AS transaction_start_time,
    sa.query AS current_query
FROM
    pg_locks l
JOIN
    pg_class c ON l.relation = c.oid
JOIN
    pg_stat_activity sa ON l.pid = sa.pid
WHERE
    c.relname = 'your_target_table'  -- 替换为你的目标表名
    AND l.locktype = 'relation'
    AND l.mode IN ('ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE')  -- DML操作持有的典型表级锁
    AND sa.pid != pg_backend_pid()  -- 排除当前会话
    AND sa.state IN ('active', 'idle in transaction');  -- 筛选未提交事务的会话

各部分作用说明：

• pg_locks：记录当前所有持有的锁，通过relation关联到目标表；locktype='relation'指定表级锁，mode筛选DML相关锁——INSERT/UPDATE/DELETE默认持有ROW EXCLUSIVE锁，部分特殊DML或LOCK TABLE语句会用SHARE UPDATE EXCLUSIVE锁。
• pg_stat_activity：提供会话详细信息，xact_start是事务启动时间；active表示正在执行语句的活跃事务，idle in transaction表示事务未提交的空闲会话。
• pg_class：通过表名关联到系统内部对象ID，实现锁与目标表的绑定。

要让当前会话等待目标表的所有未提交DML事务完成（提交或回滚），可以编写循环检查的匿名块，定期扫描直到无待处理会话：

DO $$
DECLARE
    pending_count INT;
    target_table TEXT := 'your_target_table';  -- 替换为你的目标表名
BEGIN
    LOOP
        -- 统计未提交的DML会话数量
        SELECT COUNT(*) INTO pending_count
        FROM pg_locks l
        JOIN pg_class c ON l.relation = c.oid
        JOIN pg_stat_activity sa ON l.pid = sa.pid
        WHERE
            c.relname = target_table
            AND l.locktype = 'relation'
            AND l.mode IN ('ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE')
            AND sa.pid != pg_backend_pid()
            AND sa.state IN ('active', 'idle in transaction');

        -- 无待处理会话则退出循环
        IF pending_count = 0 THEN
            EXIT;
        END IF;

        -- 每隔1秒检查一次，可按需调整间隔
        PERFORM pg_sleep(1);
    END LOOP;
END $$;

• 锁模式覆盖：如果有特殊操作（比如LOCK TABLE ... IN SHARE MODE），需调整mode取值范围，确保覆盖所有相关未提交事务。
• 权限要求：执行查询需要pg_monitor角色权限，否则可能无法查看其他会话的详细信息。
• 性能影响：循环等待会占用当前会话资源，检查间隔不宜过短，避免频繁查询系统视图带来性能开销。

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