Autovacuum Hung Due to Bufferpin

From: Fred Habash <fmhabash(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Autovacuum Hung Due to Bufferpin
Date: 2023-01-11 13:59:38
Message-ID: CADpeV5xH6pjTO-fX8gufAXN2r_09C6WVNsDdriWpWV5tkyV-Ug@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a very small table of 28 rows that is less than 10kB. One of the AV
workers has been stuck autovac'ing it for over 20 hrs now with no progress
in heap blocks scanned or vac'd. I terminated the AV worker and ran a
manual vac which also ended up stuck waiting for a bufferpin.

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

I have also disabled AV for the table, but AV does not appear to honor that
due to 'to prevent wraparound'.

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?

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2023-01-11 15:32:10 Re: Autovacuum Hung Due to Bufferpin
Previous Message hubert depesz lubaczewski 2023-01-11 12:54:03 Re: Disallow execution of shell commands from psql