Re: Auto-vacuum is not running in 9.1.12

Prakash Itnal wrote:

> Currently the issue is easily reproducible. Steps to reproduce:
> * Set some aggressive values for auto-vacuuming.
> * Run a heavy database update/delete/insert queries. This leads to invoking
> auto-vacuuming in quick successions.
> * Change the system time to older for eg. 1995-01-01
>
> Suddenly auto-vacuuming stops working. Even after changing system time back
> to current time, the auto-vacuuming did not resume.
>
> So the question is, "does postrges supports system time changes?".

So Tom Lane just pinged me about this. As far as I can tell, the
problem is that the clock goes backwards 20 years, then autovacuum
figures that it needs to sleep for 20 years until the next vacuum is
scheduled. Then regardless of the clock moving forwards again, the
sleep is not affected by this. (I didn't actually test this.)

A simple workaround would be to stop autovacuum then restart it, that
is, set autovacuum=off in postgresql.conf, send SIGHUP to postmaster
which should stop the autovacuum launcher, then set autovacuum=on and
SIGHUP again, which would start a new launcher.

As a proposed code fix, we could just clamp the sleep time to, say, 5
minutes. In that case the launcher would wake up even if the next
database check is still a ways off, but that should be pretty harmless
-- we just compute a new sleep period and continue sleeping. (That is,
notwitshstanding the fact that the sleep time should never really go
above a minute in most configurations.)

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services