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.
The 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.