Re: Idle in transaction

On Fri, Jul 17, 2009 at 8:05 AM, Sharma, Sid<ssharma(at)bjs(dot)com> wrote:
> Hi
>
> I’m a postgres newbie. I just implemented a new web application using
> postgres.
>
> When I look at the db connections (via ps), I notice that all existing
> connections are in ‘Idle in Transaction’ state.
>
> They never go to idle state.

Then it's likely a bug in your application / connection / pooling
software. I'm gonna guess you're using Java and jdbc. But it could
be something else.

> The application itself remains functional and responsive. It has been up for
> over 36 hours now without any issues.

Many ships stay afloat for hours after suffering fatal collisions with
icebergs. The likely problem here will be that your tables will
slowly bloat with dead tuples because vacuum can't reclaim space.
Eventually either your db will slow to a crawl or you'll run out of
disk space.

> What is the significance of this state? Does this imply a transaction leak?

No, it implies broken application / connection / pooling code.

> Then why am I not noticing deadlocks, timeouts etc.

Because that's not what happens when connections are left idle in transaction.