-----Original Message----- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 16 February 2017 22:40 To: Tim Bellis <tim.bel...@metaswitch.com> Cc: Adrian Klaver <adrian.kla...@aklaver.com>; pgsql-general@postgresql.org; Alvaro Herrera <alvhe...@2ndquadrant.com>; Scott Marlowe <scott.marl...@gmail.com> Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries
Tim Bellis <tim.bel...@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 getting indexes 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 order to alter the table's schema. > The ALTER is queued waiting for the vacuum to finish, and lesser lock > requests queue up behind it. We could let the non-exclusive lock requests go > ahead of the ALTER, but that would create a severe risk of the 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_process for the queries reading the index data was the autovacuum, not the ALTER. Am I reading the 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 so long 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
