The other day after doing some database migrations my web app suddenly froze - all requests timing out. I started a psql shell to investigate if I could reproduce it.

$ psql -c "SELECT * FROM table LIMIT 1;"

And yes, any select on a particular table would just hang forever.

This was probably due to some other query being granted a lock on the table but either hanging on something or just frozen. To find which one, list the not granted locks.

SELECT * FROM pg_locks WHERE NOT GRANTED;
postgres=# SELECT * FROM pg_locks WHERE NOT GRANTED;
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
 relation |    18353 |    18520 |      |       |            |               |         |       |          | 35/281590          | 32558 | AccessShareLock     | f       | f
 relation |    18353 |    18520 |      |       |            |               |         |       |          | 31/453979          | 32289 | AccessExclusiveLock | f       | f
 relation |    18353 |    18520 |      |       |            |               |         |       |          | 30/708323          | 31110 | AccessExclusiveLock | f       | f
 relation |    18353 |    18520 |      |       |            |               |         |       |          | 34/358791          | 32475 | AccessExclusiveLock | f       | f
(4 rows)

This will basically show all locks waiting to be granted. In other words if any process is waiting for a lock on a table. If nothing shows up, start a query in the background. Just so something hangs.

Copy the relation from the table. For me this was 18520. Then search for all locks on that relation:

postgres=# SELECT * FROM pg_locks WHERE relation = 18520;
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
 relation |    18353 |    18520 |      |       |            |               |         |       |          | 35/281590          | 32558 | AccessShareLock     | f       | f
 relation |    18353 |    18520 |      |       |            |               |         |       |          | 31/453979          | 32289 | AccessExclusiveLock | f       | f
 relation |    18353 |    18520 |      |       |            |               |         |       |          | 33/7141            |  5144 | AccessShareLock     | t       | f
 relation |    18353 |    18520 |      |       |            |               |         |       |          | 30/708323          | 31110 | AccessExclusiveLock | f       | f
 relation |    18353 |    18520 |      |       |            |               |         |       |          | 34/358791          | 32475 | AccessExclusiveLock | f       | f
(5 rows)

In short relations are things that look and behave like views and tables. You can find more information about this in this this excellent Stack Overflow answer.

Notice that one of them has granted = t:

 relation |    18353 |    18520 |      |       |            |               |         |       |          | 33/7141            |  5144 | AccessShareLock     | t       | f

What you’re interested in here is the pid. Because it lets you identify the actual query that’s running. You can now find information about this query by looking at the pg_stat_activity table.

My PID was 5144.

postgres=# SELECT query, xact_start, query_start, backend_start, state_change, state FROM pg_stat_activity WHERE pid IN (5144);

This gives you the status, what the actual query was, when it was started and so on. For me it was just a select query. So I concluded it was okay to just stop it. I first tried to cancel it:

postgres=# SELECT pg_cancel_backend(5144);

But even though it returned t the query wasn’t stopped. Everything was still hanging.

So then I forcibly terminated it with:

postgres=# SELECT pg_terminate_backend(5144);

And everything went back to normal.

If the hanging query was an update, delete or insert I’d perhaps spent some more time backing it up and investigating later if for example the query would have had to been run later manually.

PostgreSQL is usually very nice to work with.