In our POSTGRESQL environment, we always have an issue of frequent locking and blocking. And as a DBA we have to find blocking and blocked PID’s.

Many times we have seen that the user contacts the DBA team indicating that the  process is not responding and getting stuck every now and then.

So as a DBA checking the applicable database blocking is the first step you can try. Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock. This forces the second connection to be blocked until the first connection completes. With this being said, locking is a natural occurrence in POSTGRESQL in order to maintain data integrity.

Here is a query to check the blocking in POSTGRESQL.

PostgreSQL 9.6 and later:

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;

PostgreSQL 9.5 and earlier:

SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
age(now(), blocking_activity.query_start) AS "age",
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process,
blocked_activity.application_name AS blocked_application,
blocking_activity.application_name AS blocking_application
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;