Re: Autovacuum Truncation Phase Loop?

On Sat, 2020-12-12 at 12:08 -0500, Jeff Janes wrote:
> On Mon, Nov 9, 2020 at 11:43 AM Creston Jamison <creston(dot)jamison(at)rubytreesoftware(dot)com> wrote:
> > 2020-11-04 16:34:47.635 UTC [894681-1] ERROR: canceling autovacuum task
> > 2020-11-04 16:34:47.635 UTC [894681-2] CONTEXT: automatic vacuum of table "x.pg_toast.pg_toast_981540"
> >
> > Based upon Googling, we suspect it is the truncation step of autovacuum and its ACCESS EXCLUSIVE lock attempt(s).
> >
>
> No, I think that would lead to different messages explicitly mentioning truncation.
> (or no messages in most cases, as I think those messages only get logged either for
> 'vacuum verbose' or when log_min_messages = debug2 or higher). So something else is going on.
>
> Are these vacuums happening for wrap around? I don't know why else they would
> restart so aggressively. On the other hand if they were for wrap around, they
> shouldn't be allowing themselves to get canceled so easily in the first place.

Right.

Autovacuum gets canceled if it blocks another statement for more than a second.

Likely candidates are: concurrent manual VACUUM, CREATE/DROP INDEX, CREATE/DROP TRIGGER,
ALTER/DROP TABLE, ALTER TABLE, TRUNCATE, or (most often) an explicit LOCK TABLE.

If that happens all the time for that table, you'll get a problem eventually.

Yours,
Laurenz Albe

--
Cybertec | https://www.cybertec-postgresql.com