Skip site navigation (1) Skip section navigation (2)

Re: fighting '<IDLE> in transaction'

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Vladimir Rusinov <vladimir(at)greenmice(dot)info>
Cc: Lewis Kapell <lkapell(at)setonhome(dot)org>, pgsql-admin(at)postgresql(dot)org
Subject: Re: fighting '<IDLE> in transaction'
Date: 2009-11-05 17:53:59
Message-ID: dcc563d10911050953l243a4f6dwd0cab5f046bd8ab0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Thu, Nov 5, 2009 at 7:16 AM, Vladimir Rusinov
<vladimir(at)greenmice(dot)info> wrote:
>
>
> On Tue, Nov 3, 2009 at 5:54 PM, Lewis Kapell <lkapell(at)setonhome(dot)org> wrote:
>>
>> Please see these earlier threads:
>>
>> Is IDLE session really idle?
>> http://archives.postgresql.org/pgsql-admin/2009-06/msg00096.php
>>
>> Idle connections
>> http://archives.postgresql.org/pgsql-admin/2009-10/msg00017.php
>
> Thanks - that was useful reading, but not related to my issue. There are not
> a lot of IDLE sessions: connection pools are configured accurate. There are
> problems with 'IDLE in transaction' sessions, which I believe may cause
> strong lock problems.

The real issue with idle in transaction isn't locking so much.  A
simple idle in transaction that just ran a select * from table limit
1; will have made it so that vacuum cannot reclaim space that it
normally could until that transaction is committed or rolled back.  So
it's much worse than simple locking of a few records, it's causing
your data store to bloat with all the otherwise reclaimable space
since that transaction began.  Let it run for a day or a week and a
busy database will be expanding until it's slow and possibly running
out of space.

In response to

Responses

pgsql-admin by date

Next:From: Alvaro HerreraDate: 2009-11-05 17:57:52
Subject: Re: fighting '<IDLE> in transaction'
Previous:From: Kevin GrittnerDate: 2009-11-05 16:40:01
Subject: Re: pg_stat_activity howto

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group