Re: Allow single table VACUUM in transaction block

Hi,

On Fri, Nov 4, 2022 at 2:39 PM Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
wrote:

> Hi Rahila,
>
> Thanks for your review.
>
> On Fri, 4 Nov 2022 at 07:37, Rahila Syed <rahilasyed90(at)gmail(dot)com> wrote:
>
> >> I would like to bring up a few points that I came across while looking
> into the vacuum code.
> >>
> >> 1. As a result of this change to allow VACUUM inside a user
> transaction, I think there is some chance of causing
> >> a block/delay of concurrent VACUUMs if a VACUUM is being run under a
> long running transaction.
> >> 2. Also, if a user runs VACUUM in a transaction, performance
> optimizations like PROC_IN_VACUUM won't work.
> >> 3. Also, if VACUUM happens towards the end of a long running
> transaction, the snapshot will be old
> >> and xmin horizon for vacuum would be somewhat old as compared to
> current lazy vacuum which
> >> acquires a new snapshot just before scanning the table.
> >>
> >> So, while I understand the need of the feature, I am wondering if there
> should be some mention
> >> of above caveats in documentation with the recommendation that VACUUM
> should be run outside
> >> a transaction, in general.
> >>
> >
> > Sorry, I just noticed that you have already mentioned some of these in
> the documentation as follows, so it seems
> > it is already taken care of.
> >
> > + <command>VACUUM</command> cannot be executed inside a transaction
> block,
> > + unless a single table is specified and <literal>FULL</literal> is
> not
> > + specified. When executing inside a transaction block the vacuum
> scan can
> > + hold back the xmin horizon and does not update the database
> datfrozenxid,
> > + as a result this usage is not useful for database maintenance, but
> is provided
> > + to allow vacuuming in special circumstances, such as temporary or
> private
> > + work tables.
>
> Yes, I wondered whether we should have a NOTICE or WARNING to remind
> people of those points?
>

+1 . My vote for NOTICE over WARNING because I think
it is useful information for the user rather than any potential problem.

Thank you,
Rahila Syed