PostgreSQL获取阻塞与被阻塞用户用户名返回空数组问题

这个问题我之前也碰到过，核心原因是子查询里的pid作用域不对，导致关联的进程ID完全错配，所以返回[null]。

问题分析 #

你原来的SQL里，嵌套子查询：

(select usename from pg_stat_activity where pid=ANY(pg_blocking_pids(pid)))

这里的第二个pid是子查询内部pg_stat_activity表的列，而不是外部查询中被阻塞进程的pid。相当于子查询在遍历自身的每一行时，调用pg_blocking_pids(pid)，而不是针对外部的被阻塞进程ID去查询，自然匹配不到正确的阻塞用户，返回null。

而你手动传入固定PID（比如14648）时，pg_blocking_pids(14648)明确指向了被阻塞进程的阻塞列表，所以能正确查到用户。

修正后的SQL写法 #

推荐用LATERAL子查询来确保子查询能正确引用外部查询的pid，这样可以准确关联到阻塞进程的用户名：

SELECT 
  p.pid AS blocked_pid,
  p.usename AS blocked_user,
  pg_blocking_pids(p.pid) AS blocking_pids,
  b.usename AS blocking_user
FROM pg_stat_activity p
LEFT JOIN LATERAL (
  SELECT usename 
  FROM pg_stat_activity 
  WHERE pid = ANY(pg_blocking_pids(p.pid))
) b ON true
WHERE cardinality(pg_blocking_pids(p.pid)) > 0;

如果一个被阻塞进程被多个进程阻塞，想要把所有阻塞用户聚合到数组里，可以用array_agg：

SELECT 
  p.pid AS blocked_pid,
  p.usename AS blocked_user,
  pg_blocking_pids(p.pid) AS blocking_pids,
  array_agg(b.usename) AS blocking_users
FROM pg_stat_activity p
LEFT JOIN pg_stat_activity b ON b.pid = ANY(pg_blocking_pids(p.pid))
WHERE cardinality(pg_blocking_pids(p.pid)) > 0
GROUP BY p.pid, p.usename;

验证效果 #

这两种写法都会正确关联外部被阻塞进程的pid，调用pg_blocking_pids(p.pid)获取阻塞进程列表后，再匹配对应的用户名，不会再返回null数组了。

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