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. 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.
>
> > 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.
>
> The way this works is that deadlock checker returns "you're blocked but
> the culprit is autovacuum" when not in emergency mode; then the other
> process sends a signal to autovac which commits suicide. To fix this
> case we would have to add another special code "you're blocked but the
> culprit is an emergency vacuum", then the other process sees itself as
> truncate then sends signal anyway.
>

​Related question: does a truncated table effectively reset its wraparound
counter back to zero?

Put another way: what is the use case we are trying​

​to support here?

If the table in question is being truncated regularly it doesn't seem like
this situation should arise. If it is not a regularly truncated table the
possibility of a random truncation being run simultaneously with a
wraparound VACUUM seems quite small. If the truncate is being run
administratively then whomever is running that should at least be able to
see the wraparound vacuum: whether they can get superuser rights and do
something about it is another matter. Even then, supposing that TRUNCATE
is a viable means to speed up a wraparound vacuum seems a bit of a stretch
in the general case. Since pg_cancel_backend is usable in this situation
putting code into core to handle this scenario does not seem like a good
idea.

David J.