Re: autovacuum on primary blocking queries on replica?

On Fri, May 27, 2022 at 3:01 PM Don Seiler <don(at)seiler(dot)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

>