After much thought and discussion, here is my proposal of how to handle autovacuum workers which block out user-initiated SQL statements.
Autovacuum workers running VACUUM, VACUUM ANALYZE and ANALYZE can give problems by blocking out other users in various circumstances. There are good cases for all 3 statements to be cancelled, so we don't bother to enquire what exactly the AV worker is actually doing before we cancel it. If an AV worker task is cancelled it will be scheduled to run again on the next cycle. It is possible, though unlikely that this will result in indefinite starvation; in that case existing log message will warn the user. We check for blocking AV workers when we enter the deadlock detector. At this stage we must allow the deadlock detector to proceed to its conclusion in case any hard or soft deadlocks exist. It is possible that our lock queue waits on an AV worker *and* we are part of a deadlock with a third process, so we must just let the detector roll. If we find no deadlock, yet the head of the wait queue is an AV worker, then we send a cancel (SIGINT) to that process. It's possible for multiple backends to do this, though that shouldn't be a problem. If an AV worker is not a current lock holder we wait normally, since it is presumed the user knows about other conflicting lock holders and would have to have waited anyway. In that case the AV worker is not cancelled. That aspect is changeable with some thought. A patch that implements this is being sent to -patches now. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
