I have a postgres 9.3.4 database table which (intermittently but reliably) gets into a state where queries get blocked indefinitely (at least for many hours) behind an automatic vacuum. I was under the impression that vacuum should never take any blocking locks for any significant period of time, and so would like help resolving the issue.
The process blocking the query is: postgres 21985 11304 98 Feb13 ? 1-14:20:52 postgres: autovacuum worker process <db_name> which is running the query autovacuum: VACUUM public.<table_name>
[Tim Bellis] It doesn’t. I was using the query from https://wiki.postgresql.org/wiki/Lock_Monitoring and looking at the ‘current_statement_in_blocking_process’ column. Is there a different query I should be using?
That query seems to be a bit mangled. At one time, it only found row-level locks. Someone changed that, but didn't remove the comment "these only find row-level locks, not object-level locks"
Also, the "WHERE NOT blocked_locks.GRANTED" should perhaps be:
WHERE NOT blocked_locks.GRANTED and blocking_locks.GRANTED;
As it is, every waiting query reports that it is waiting on all of its fellow victims as well as the thing(s) actually blocking it. But my WHERE clause is not really correct either, as it is possible that it is one blocked thing is being blocked by a different blocked thing which is ahead of it in the queue, when without that intervening blocked requestor it could be immediately granted if its request mode is compatible with the held mode(s). I don't think there is a query that can reveal what is most immediately blocking it.
But, I don't see how this explains what you see. An autovacuum without "(to prevent wraparound)" should not block anything for much more than a second (unless you changed deadlock_timeout) and should not be blocked by anything either as it just gives up on the operation if the lock is not immediately available.