Re: Manual anti-wraparound vacuums

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Nov 11, 2011 at 9:59 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> In another thread, Alvaro quoted from the manual:
> If for some reason autovacuum fails to clear old XIDs from a table, the
> system will begin to emit warning messages like this when the database's oldest
> XIDs reach ten million transactions from the wraparound point:
>>
> WARNING: database "mydb" must be vacuumed within 177009986 transactions
> HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".
>>
> (A manual VACUUM should fix the problem, as suggested by the hint; but
> note that the VACUUM must be performed by a superuser, else it will fail to
> process system catalogs and thus not be able to advance the database's
> datfrozenxid.)
>>
>> It occurs to me to wonder how this scenario will interact with the
>> recent changes to let VACUUM skip pages. AFAIR there is not a way for a
>> manual VACUUM to set the anti-wraparound mode, is there?

> I tweaked Simon's original patch to address exactly this scenario;
> VACUUM FREEZE prevents page-skipping behavior.

That doesn't address my concern. (1) The manual does not say you must
use VACUUM FREEZE for this, nor do the HINT messages. (2) You probably
wouldn't want to use VACUUM FREEZE, as that could force a great deal
more I/O than might be necessary to fix the problem. (3) In disaster
recovery scenarios, the last thing we want is to be imposing extra
conditions on what an already-stressed DBA has to do to fix things;
especially extra conditions that are different from the way it's worked
for the last ten years. And there's also (4) if someone is doing a
manual VACUUM, they might well wish the table to be completely vacuumed,
not just sort of.

I think we'd be better advised to restrict the page-skipping behavior
to autovacuums, period, and remove the connection to FREEZE.

regards, tom lane