Re: [GENERAL] Autovacuum stuck for hours, blocking queries

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 February 2017 22:40
To: Tim Bellis <Tim.Bellis@metaswitch.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org; Alvaro Herrera <alvherre@2ndquadrant.com>;
ScottMarlowe <scott.marlowe@gmail.com> 
Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Tim Bellis <Tim.Bellis@metaswitch.com> writes:
> > Even though this is a read only query, is it also expected to be blocked behind the vacuum? Is there a way of
gettingindexes for a table which won't be blocked behind a vacuum? 

> It's not the vacuum that's blocking your read-only queries.  It's the ALTER TABLE, which needs an exclusive lock in
orderto alter the table's schema.  The ALTER is queued waiting for the vacuum to finish, and lesser lock requests queue
upbehind it.  We could let the non-exclusive lock requests go ahead of the ALTER, but that would create a severe risk
ofthe ALTER *never* getting to run. 

The lock monitoring query (https://wiki.postgresql.org/wiki/Lock_Monitoring) said that the blocking_pid and the
current_statement_in_blocking_processfor the queries reading the index data was the autovacuum, not the ALTER. Am I
readingthe output wrong? Does it not correctly represent the chain of locks? 

> I'd kill the ALTER and figure on trying again after the vacuum is done.

> Also you might want to look into how you got into a situation where you have an anti-wraparound vacuum that's taking
solong to run. 
> You didn't do something silly like disable autovacuum did you?
No, autovacuum is on (and this is an autovacuum which is in progress). But I will look at why I'm getting a blocking
autovacuum.

>            regards, tom lane