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.jamison@rubytreesoftware.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