It's not uncommon for various elements of the database to block each other. Queries can lock shared resources, system maintenance can temporarily prevent a transaction from committing; the list is endless. As a result, a critical aspect of troubleshooting a PostgreSQL system is tracking down blocked systems, and what might be preventing normal operation.
There are two very powerful ways to decipher locks within PostgreSQL in the
pg_locks view and the new PostgreSQL 9.6
pg_blocking_pids function. Let's see why these approaches are so useful.
pg_locks view needs no special access for use, and the
pg_blocking_pids function can be called by any user. However, these resources are of limited utility without full access to
pg_stat_activity as well. To proceed with this recipe, either connect to the database as a superuser (such as the
postgres user), or refer to the Checking the pg_stat_activity view recipe to circumvent this limitation.