-----Original Message----- From: Tom Lane [mailto:[email protected]] Sent: 16 February 2017 22:40 To: Tim Bellis <[email protected]> Cc: Adrian Klaver <[email protected]>; [email protected]; Alvaro Herrera <[email protected]>; Scott Marlowe <[email protected]> Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries
Tim Bellis <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
