Re: Manual anti-wraparound vacuums

On Nov 11, 2011, at 10:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

If relfrozenxid needs advancing, that also prevents pages from being skipped. So I think there's no problem here. We are just making a very minor extension of a behavior that has existed since 8.4.

...Robert