Deciphering database locks

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.

Getting ready

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.

How to do it...

Create...