Re: autovacuum on primary blocking queries on replica?

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: autovacuum on primary blocking queries on replica?
Дата
Msg-id CAMkU=1yaL4zav_LKL+RGNOyZ53go1YncMSb=1zGZ7qML8=DNGQ@mail.gmail.com
обсуждение исходный текст
Ответ на autovacuum on primary blocking queries on replica?  (Don Seiler <don@seiler.us>)
Список pgsql-general
Дерево обсуждения
On Fri, May 27, 2022 at 3:01 PM Don Seiler <don@seiler.us> wrote:

I've been reading tales of autovacuum taking an AccessExclusiveLock when truncating empty pages at the end of a table. I'm imagining that updating every row of a table and then rolling back would leave all of those rows empty at the end and qualify for truncation and lead to the scenario I saw this morning.

That is likely.
 

I'm still not entirely satisfied since that table in question was so small (only 8252 rows) so I wouldn't imagine it would hold things up as long as it did. Although the blocking session on the replica was an application session, not any background/recovery process.

Yeah, so to me the interesting question is, what was that application session doing for so long?

Once the recovery process acquires the lock it needs, it would presumably only hold it for a short amount of time.  But it has to get the lock first, which it can't do due to that other process camping on the access share lock.  And once it places itself in the queue for the lock, any newcomers can't jump over it.  Even if the new entrants only want an access share lock, they are stuck behind the access exclusive lock request, which is in turn stuck behind the already granted share lock.

Cheers,

Jeff

В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: JSONB index not in use, but is TOAST the real cause of slow query?
Следующее
От: Matthias Apitz
Дата:
Сообщение: Re: existing row not found by SELECT ... WHERE CTID = ?