Re: Manual anti-wraparound vacuums

On 11.11.2011 17:47, Tom Lane 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:
>>> 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