Postgres 9.6.6 on CentOS 7
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.
In pg_locks, I see a handful of sessions that have an AccessShareLock on the table I'm trying to vacuum. My vacuum session has a ShareUpdateExclusiveLock on that relation. All of those sessions look like orphaned reporting sessions sitting "idle in transaction". It's unclear to me why a report job would end up idle in transaction, to my knowledge we don't disable autocommit from the reporting app server.
Anyway, my next step is getting the OK to terminate those idle in transaction sessions to see if that gets my vacuum job moving. Meanwhile I'll ask a dev to sort out why they might be sitting idle in transaction, there's no reason for them to be unless the app server connection died and they are zombies. However I'm curious if there is someplace else I could/should also look to get to the root cause of this.
