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);
On Wed, Jan 11, 2023 at 10:32 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Fred Habash <fmhabash@gmail.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