Re: Autovacuum Hung Due to Bufferpin

According to pgstattuple, dead_tuple_count = 0. If this is the case, then
what other explanations do we have? I mean, how can I find out what blocker
session is holding the bufferpin to terminate it?

SELECT * FROM pgstattuple('****.*****'::regclass);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
8192 | 28 | 2224 | 27.15 | 0 |
0 | 0 | 5764 | 70.36
(1 row)

On Wed, Jan 11, 2023 at 10:32 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Fred Habash <fmhabash(at)gmail(dot)com> writes:
> > pg_locks shows no blockers while this is happening. This view shows a
> > constant 13 sessions running SELECT statements on this table posting
> > AccessShareLock. Of course, these is also the AV sessions
> > with ShareUpdateExclusiveLock
> > ...
> > Why is AV blocked by bufferpin given the fact that this table does not
> get
> > an DML changes. It is purely read only. What can be done to resolve this?
>
> Apparently there has been some DML on it in the past, leaving dead rows
> that vacuum now needs to clean up --- but it needs a transient buffer
> lock for long enough to do that. If you have a constant stream of readers
> it will never be able to get that lock. You'll need to find a way to
> momentarily block those readers.
>
> regards, tom lane
>

--

----------------------------------------
Thank you