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

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.

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

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.

> > This is especially inconvenient when the user trying to truncate does
> > not have permission to kill (or even see!) the wraparound VACUUM.
> > Would it be possible to have autovacuum yield to a TRUNCATE in this
> > situation?
>
> Sounds like we would have to special-case truncate in the deadlock
> checker or something like that. Doesn't seem particularly palatable.
>

Yeah, it would be a messy case to implement, but if there were a general
way to know the locker's intentions there are other places this would come
in handy as well.

Cheers,

Jeff