Check the blocking sessions or queries in the PostgreSQL database
Check the user or process id which causes blocking:
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;
Check to block with the following query by using pg_blocking_pid function.
Pg_blocking_pid function is used to identify the blocking process id which causes another session to wait. Function returns array of PIDS which can be used in the following query with ANY clause.
SELECT
activity.pid,
activity.usename,
activity.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
Check the duration of blocking caused and also identified the table locked.
SELECT w.query AS waiting_query,
w.pid AS waiting_pid,
w.usename AS waiting_user,
now() - w.query_start AS waiting_duration,
l.query AS locking_query,
l.pid AS locking_pid,
l.usename AS locking_user,
t.schemaname || '.' || t.relname AS tablename,
now() - l.query_start AS locking_duration
FROM pg_stat_activity w
JOIN pg_locks l1 ON w.pid = 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.pid
JOIN pg_stat_user_tables t ON l1.relation = t.relid
You can terminate all blocking queries:
SELECT pg_cancel_backend(a.pid)
FROM( select pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0) a
You can also remove blocking by terminating the sessions:
SELECT pg_terminate_backend(a.pid)
FROM( select pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0) a