On Tue, Sep 8, 2020 at 8:49 PM Andres Freund <andres@anarazel.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?
