Database Reference
In-Depth Information
Who is blocking them?
Once you have found out that some query is blocked, you need to know who or what is
blocking them.
Getting ready
Same as others, just use any superuser account to run the queries.
How to do it...
Run the following query:
SELECT
w.current_query as waiting_query,
w.procpid as w_pid,
w.usename as w_user,
l.current_query as locking_query,
l.procpid as l_pid,
l.usename as l_user,
t.schemaname || '.' || t.relname as tablename
from pg_stat_activity w
join pg_locks l1 on w.procpid = l1.pid and not l1.granted
join pg_locks l2 on l1.relation = l2.relation and l2.granted
join pg_stat_activity l on l2.pid = l.procpid
join pg_stat_user_tables t on l1.relation = t.relid
where w.waiting;
It returns process ID, user, and current query about both blocked and blocking backends, and
also the schema and table name of the table that causes the blocking.
How it works...
This query first selects all waiting queries (where
w.waiting
), then gets the locks on which
this query is waiting (
join
pg_locks
l1
on
w.procpid
=
l1.pid
and not
l1.granted
),
then looks up the lock which is granted on the same table (
join
pg_locks
l2
on
l1.relation
=
l2.relation
and
l2.granted
), and finally looks up a row in
pg_stat_
activity
corresponding to the granted lock. It also resolves the relation identifier (relid) of
the table to its full name using system view
pg_stat_user_tables
.
