Re: Allow single table VACUUM in transaction block

On Fri, Nov 18, 2022 at 7:04 AM Simon Riggs
<simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> Outside a transaction - works perfectly
> In a transaction - throws ERROR, which prevents a whole script from
> executing correctly

Right, but your proposal would move that inconsistency to a different
place. It wouldn't eliminate it. I don't think we can pretend that
nobody will notice their operation being moved to the background. For
instance, there might not be an available background worker for a long
time, which could mean that some vacuums work right away and others
just sit there for reasons that aren't obvious to the user.

> So if consistency is also a strong requirement, then maybe we should
> make that new command the default, i.e. make VACUUM always just a
> request to vacuum in background. That way it will be consistent.

Since one fairly common reason for running vacuum in the foreground is
needing to vacuum a table when all autovacuum workers are busy, or
when they are vacuuming it with a cost limit and it needs to get done
sooner, I think this would surprise a lot of users in a negative way.

> Can we at least have a vacuum_runs_in_background = on | off, to allow
> users to take advantage of this WITHOUT needing to rewrite all of
> their scripts?

I'm not entirely convinced that's a good idea, but happy to hear what
others think.

--
Robert Haas
EDB: http://www.enterprisedb.com