On Wed, Sep 9, 2020 at 12:58 AM Andres Freund <and...@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? -- Magnus Hagander Me: https://www.hagander.net/ <http://www.hagander.net/> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
