Re: VACUUM (INTERRUPTIBLE)?

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

Greetings,

Andres Freund