Re: VACUUM (INTERRUPTIBLE)?

On Wed, Sep 9, 2020 at 12:58 AM Andres Freund <andres(at)anarazel(dot)de> wrote:

> Hi,
>
> On 2020-09-08 22:30:40 +0200, Magnus Hagander wrote:
> > One thing I've been wanting many times but never properly got around to
> > investigating how much work it would be to make happen, was to be able to
> > trigger an autovacuum manually (yeah, strange choice of terms). That is,
> > instead of the above, you'd have something like "VACUUM BACKGROUND" which
> > would trigger an autovacuum worker to do the work, and then release your
> > session. The points then being both (1) the ability to interrupt it, and
> > (2) that it'd run in the backgorund and thus the foreground session could
> > disconnect.
> >
> > I think both would probably solve your problem, and being able to
> trigger a
> > background one would add some extra value? But we'd have to figure out
> and
> > be clear about what to do if all workers are busy for example - queue or
> > error?
> >
> > Worth considering, or am I missing something?
>
> It seems like it could be useful in general. Not that much for my case
> however. It'd be much easier to test whether vacuum was successfully
> cancelled if we can see the cancellation, which we can't in the
> autovacuum case. And there'd likely be some fairly ugly logic around
>

That does bring up the other thing that I even put together some hacky
patch for at one point (long since lost or badly-rebased-into-nothingness)
which is to have the stats collector track on a per-relation basis the
number of autovacuum interruptions that have happened on a specific table :)

But yes, with that it would still not be *as easy* to use, definitely.

> needing to wait until the "autovacuum request" is processed etc,
> including the case that all workers are currently busy.

> So my INTERRUPTIBLE idea seems to be a better fit for the tests, and
> independently quite useful. E.g. wanting to know whether VACUUM errored
> out is useful for scripts that want their VACUUMs to be interruptible,
> and that doesn't work well with the "backgrounding" idea of yours.
>
> Having said that, your idea does seem like it could be helpful. The
> difficulty seems to depend a bit on the exact desired
> semantics. E.g. would the "queue" command block until vacuum started or
> not? The latter would obviously be much easier...

Yeah, that's where I stalled in my own thoughts about it I think. OTOH, why
wait for it to start, if you're not waiting for it to finish... But also,
if there is a max size of the queue, what do you do if you hit that one?

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>