Re: Vacuum process waiting on BufferPin

On 2018-Aug-13, Don Seiler wrote:

> This afternoon I discovered an autovacuum process that had been running for
> over 6 days. It was waiting on BufferPin event. I kicked off a manual
> vacuum+analyze of the table, which automatically killed that autovacuum.
> This ran for a few minutes before it too was waiting on a BufferPin event.
> I've never witnessed a vacuum session waiting on BufferPin before.

Buffer pins are normally released quickly enough. Of course, an
idle-in-transaction session has its own problems, but even those would
normally not have buffer pins; so vacuum would run to completion without
blocking, even if no tuples would be removed.

Maybe you had a cursor that was not fully scanned before the session was
left idle -- as I recall, those can leave buffers pinned.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services