Re: BUG #13002: VACUUM to prevent wraparound blocks TRUNCATE - Mailing list pgsql-bugs

Jeff Janes wrote:
> On Wed, Apr 8, 2015 at 12:21 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
> wrote:
>
> > maciek@heroku.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.  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.

The issue only arises if you're constantly causing autovac workers to
terminate because of DDL or some other processing that locks the table
away from vacuum.

> > But when autovacuum sees that
> > the problem is too serious to ignore, it doesn't give way to anything;
> > if it did, it would fall prey of the same problem it's trying to avoid.
>
> It doesn't give way to anything, except "autovacuum_vacuum_cost_delay".

That in itself might be a mistake.  Perhaps it makes sense to have
emergency vacuums ignore the cost delay, but for that we need to ensure
that emergency vacuums are rare events.

> I think we need to get off of the idea that is somehow an emergency for a
> database system to have survived 200 million transactions.  Or at least, we
> need to provide a non-emergency way to do necessary routine maintenance.
>  (In this case since a truncation is desired in the first place, you could
> consider that we do provide a way even if it is only available in
> hindsight, but in general we do not.)
>
> Right now we declare it an emergency that some guy needs to change his oil,
> shutdown half the traffic in the city during the "emergency", declare that
> all the emergency vehicles responding to this emergency are only allowed to
> move at 1/3 the speed limit, and make it illegal (or at least ineffective)
> for anyone to change their oil calmly and preemptively.

Hilarious.

But the analogy needs a bit more elaboration in order to explain the
current situation.  The guy wants to change oil at 150 million
transactions, and this would cause no trouble, but every time he pulls
over to do so, there's a traffic cop who prevents him from doing so and
tells him to get back on the street and move on.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services