Re: Idle in transaction

In response to "Sharma, Sid" <ssharma(at)bjs(dot)com>:
>
> I'm a postgres newbie. I just implemented a new web application using
> postgres.

You mention that you're using PG 8.1.3, which is very old. You'll save
yourself a lot of headaches if you at least upgrade to the latest 8.1.
But that is not part of your issue, it's just a side note.

> 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.

That's bad. It means your client program is starting a transaction and
leaving it running without doing anything with it. This is an issue with
the way the client is programmed, or with the client drivers, not with
the server. The server is doing what it's told.

The reason this is bad is that PG can't properly complete maintenance if
there are transactions that are left open constantly. Eventually your
DB will fill up the entire disk with old data that can't be cleaned up.

> The application itself remains functional and responsive. It has been up
> for over 36 hours now without any issues.
>
> What is the significance of this state? Does this imply a transaction
> leak? Then why am I not noticing deadlocks, timeouts etc.

Check the design of your app. If it issues a BEGIN, then sits there, you
need to configure it to only issue a BEGIN when it's actually ready to do
some work, and issue a COMMIT when the work is complete. Simply leaving a
connection open won't cause this.

If you're not explicitly issuing a BEGIN, then it may be a bug in the
client driver, or a misunderstanding on your part as to how to use the
driver. If you tell the list what client library you're using, I'm sure
there are folks who can offer more detailed insight.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/