Re: BUG #13002: VACUUM to prevent wraparound blocks TRUNCATE

On Wed, Apr 8, 2015 at 1:09 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> Jeff Janes wrote:
> > On Wed, Apr 8, 2015 at 12:21 PM, Alvaro Herrera <
> alvherre(at)2ndquadrant(dot)com>
> > wrote:
> >
> > > maciek(at)heroku(dot)com wrote:
> > >
> > > > If autovacuum is running a VACUUM to prevent wraparound on a certain
> > > table,
> > > > it looks like it blocks TRUNCATE of that table, which would obviate
> the
> > > need
> > > > for the VACUUM in the first place (and could happen much more
> quickly).
> > > This
> > > > seems like a usability wart at best--one has to instead kill the
> > > autovacuum
> > > > (e.g., via pg_cancel_backend) and then TRUNCATE.
> > >
> > > If you had truncated the table before the issue became an emergency,
> > > vacuum would have gave way to truncate.
> >
> > It is pretty hard to argue that the passage of 200 million transactions
> > constitutes some kind of emergency.
>
> Well, admittedly 200 million is not a lot of transactions, but the fact
> remains that it's the configured max freeze age which is what causes the
> whole problem to start with. If you raise the limit to 2 billion, the
> same thing will happen, only it will be ten times less frequent.

Right, and this is my gripe. If it were increased to 2 billion, then it
expiring actually would be an emergency. At its default value, it is not
an emergency, but we act as if it were, and we provide no way to do
otherwise. The choices are between suffering true emergencies at 2 billion
transactions, or fake emergencies at 200 million (or, as you point out, 150
million). Of course I can pick some value other than 2 billion and 200
million, but there is no good time to have a fake emergency. Or a real one.

> We
> already have a lower limit to freeze tuples; if tables are scanned in
> whole every 150 million transactions, as autovacuum would normally do,
> you wouldn't see an effective shutdown at 200 million transactions.
> Those 50 million xacts should be enough breathing room, shouldn't they.
>

But that, too, is an "emergency". As far as I can tell, there is no way to
increase the frozen ID without first declaring an emergency. If an
ordinary autovac happens to touch every page, it still doesn't increase
frozen ID.

Cheers,

Jeff