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