Cancelling Blocking Autovacuums

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