Re: Manual anti-wraparound vacuums

On 11.11.2011 17:47, Tom Lane wrote:
> Robert Haas<robertmhaas@gmail.com>  writes:
>> On Fri, Nov 11, 2011 at 9:59 AM, Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>>> 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.

set vacuum_freeze_table_age=0; VACUUM;

will do the trick.

> (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.

True.

>  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.

Yeah, I think you're right. We can accomplish that by setting the 
default vacuum_freeze_age to 0. That way it's still possible to get the 
page-skipping behavior in manual VACUUMs by setting it to non-zero, but 
you get a full scan by default.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com