Re: [DOC] Document auto vacuum interruption

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

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com