On Wed, Sep 9, 2020 at 12:58 AM Andres Freund <andres@anarazel.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?
