Re: VACUUM (INTERRUPTIBLE)?

On Tue, Sep 8, 2020 at 8:49 PM Andres Freund <andres(at)anarazel(dot)de> wrote:

> Hi,
>
> Jeff Janes in [1] found that I ([2]) broke autovacuum cancellations. It
> obviously would be good to add a test for this, but it seems hard to
> have that be reliable given how long it can take for autovacuum actually
> get around to vacuum a specific table.
>
> That made me wonder if it'd be worthwhile to add an option that'd make
> user invoked VACUUM be interruptible by conflicting lock requests, just
> like autovacuum is.
>
> That's actually something I've wanted, and see other people want, a
> couple times. Sometimes one wants to vacuum a table, but not block out
> more important things like DDL. Which isn't really possible right now.
>
> So how about adding an INTERRUPTIBLE option to VACUUM and ANALYZE?
>
> Implementation wise it seems like the best way to implement this would
> be to replace PROC_VACUUM_FOR_WRAPAROUND with
> PROC_INTERRUPTIBLE_VACANALYZE or such (i.e. inverting the meaning).
>
> One question I'm a bit split on is whether we'd want to continue
> restricting the signalling in ProcSleep() to commands running VACUUM or
> ANALYZE.
>
> We could have a generic PROC_INTERRUPTIBLE_COMMAND or such, and have
> deadlock.c / proc.c trigger whether to kill based on just that, without
> looking at PROC_IS_AUTOVACUUM / PROC_IN_VACUUM.
>
> Alternatively, if we do want to restrict it to VACUUM and ANALYZE, we'd
> have to re-introduce PROC_IN_ANALYZE ;). After 12 years of not being
> used and removed just weeks ago...
>

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?

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