Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Don Seiler
On Tue, Aug 14, 2018 at 8:58 AM, Vik Fearing wrote: > > There is an idle_in_transaction_session_timeout parameter to kill > connections that are idle (in transaction) for too long. It was > implemented specifically for cases like this. Thanks for reminding me of this. I'll probably look to set

Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Vik Fearing
On 14/08/18 15:26, Don Seiler wrote: > On Tue, Aug 14, 2018 at 8:23 AM, Vick Khera > wrote: > > > The general fix is to never sit idle in transaction, but this > specific case closing the cursor seems like it will also do it. > > > Of course. I've let developmen

Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Don Seiler
On Tue, Aug 14, 2018 at 8:23 AM, Vick Khera wrote: > > > The general fix is to never sit idle in transaction, but this specific > case closing the cursor seems like it will also do it. > Of course. I've let development know that they need to sort out why it's left that way. But if they're also NO

Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Vick Khera
On Tue, Aug 14, 2018 at 9:21 AM, Don Seiler wrote: > On Mon, Aug 13, 2018 at 5:55 PM, Vick Khera wrote: > >> On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler wrote: >> >>> >>> I don't quite follow this. What circumstances would lead to this >>> situation? >>> >> >> BEGIN WORK; >> DECLARE CURSOR ...

Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Don Seiler
On Mon, Aug 13, 2018 at 5:55 PM, Vick Khera wrote: > On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler wrote: > >> >> I don't quite follow this. What circumstances would lead to this >> situation? >> > > BEGIN WORK; > DECLARE CURSOR ... ; > FETCH ...; -- for some number of fetches, which does not rea

Re: Vacuum process waiting on BufferPin

2018-08-13 Thread Vick Khera
On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler wrote: > On Mon, Aug 13, 2018 at 4:15 PM, Alvaro Herrera > wrote: > >> >> Maybe you had a cursor that was not fully scanned before the session was >> left idle -- as I recall, those can leave buffers pinned. >> > > I don't quite follow this. What circu

Re: Vacuum process waiting on BufferPin

2018-08-13 Thread Don Seiler
On Mon, Aug 13, 2018 at 4:04 PM, Don Seiler wrote: > > > 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

Re: Vacuum process waiting on BufferPin

2018-08-13 Thread Don Seiler
On Mon, Aug 13, 2018 at 4:15 PM, Alvaro Herrera wrote: > > Maybe you had a cursor that was not fully scanned before the session was > left idle -- as I recall, those can leave buffers pinned. > I don't quite follow this. What circumstances would lead to this situation? For what its worth, these

Re: Vacuum process waiting on BufferPin

2018-08-13 Thread Alvaro Herrera
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

Vacuum process waiting on BufferPin

2018-08-13 Thread Don Seiler
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