Re: [DOC] Document auto vacuum interruption

On Sat, Aug 31, 2019 at 10:51 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, Jul 26, 2019 at 1:45 AM James Coleman <jtc331(at)gmail(dot)com> wrote:
> >
> > We've discussed this internally many times, but today finally decided
> > to write up a doc patch.
> >
>
> Thanks, I think something on the lines of what you have written can
> help some users to understand the behavior in this area and there
> doesn't seem to be any harm in giving such information to the user.
>
> > Autovacuum holds a SHARE UPDATE EXCLUSIVE lock, but other processes
> > can cancel autovacuum if blocked by that lock unless the autovacuum is
> > to prevent wraparound.This can result in very surprising behavior:
> > imagine a system that needs to run ANALYZE manually before batch jobs
> > to ensure reasonable query plans. That ANALYZE will interrupt attempts
> > to run autovacuum, and pretty soon the table is far more bloated than
> > expected, and query plans (ironically) degrade further.
> >
>
> + If a process attempts to acquire a <literal>SHARE UPDATE
> EXCLUSIVE</literal>
> + lock (the lock type held by autovacuum), lock acquisition will interrupt
> + the autovacuum.
>
> I think it is not only for a process that tries to acquire a lock in
> SHARE UPDATE EXCLUSIVE mode, rather when a process tries to acquire
> any lock mode that conflicts with SHARE UPDATE EXCLUSIVE. For the
> conflicting lock modes, you can refer docs [1] (See Table 13.2.
> Conflicting Lock Modes).
>
> [1] - https://www.postgresql.org/docs/devel/explicit-locking.html

Updated patch attached. I changed the wording to be about conflicting
locks rather than a single lock type, added a link to the conflicting
locks table, and fixed a few sgml syntax issues in the original.

James Coleman