PostgreSQL obtain username of blocking and blocked users

Question

I'm trying to get the blocking and blocked user's usernames but I'm getting a null array.

This is what I've tried:

select pid, 
   usename, 
   pg_blocking_pids(pid) as blocked_by, (select usename from 
pg_stat_activity where pid=ANY(pg_blocking_pids(pid)))
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

That nested select is returning [null].

The this is that if I query that select inserting manually the pid value like this

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

I get the value I'm looking for.

Answer 1

I think you need a correlation clause:

select sa.pid, sa.usename, 
       pg_blocking_pids(sa.pid) as blocked_by,
       (select sa2.usename
        from pg_stat_activity sa2
        where sa2.pid = ANY(pg_blocking_pids(sa.pid)))
from pg_stat_activity sa
where cardinality(pg_blocking_pids(sa.pid)) > 0;

You will know that this is working if you get an error stating that the subquery returns more than one row. In that case, add array_agg() to the subquery.