Thx for all your response @Bill Moran > 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. I'm sure it is outdated. When I suggested a new application I should have said a new module within an existing application. In other words, the db pre-exists. We are rewriting our entire application(s) and moving our database to DB2 and Oracle starting pretty soon. So postgres is end of life here and so there is no stomach or interest to upgrade. > 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 module/application is read-only and so only does SELECTs. There are very infrequent writes to the database. In fact writes have not happened yet since our launch. I can monitor disk utilization to see if it's growing but since we only do queries, I would be surprised that were to be true. But then again, I do not know postgres. > 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. It's a jdbc driver. The jar file's manifest does not contain the version. I noticed the timestamp of all the files in the jar are in 2002. How do I find out? @Scott Marlowe >> Then why am I not noticing deadlocks, timeouts etc. > Because that's not what happens when connections are left idle in > transaction. Then what should I look for? This is a query-only module. It is not adding any data to the database. The data in the tables remains static. I have been watching the number of connections and that has remained static i.e. at the min level of the connection pool. @Richard Huxton > You don't say how you are connecting to the DB, but it's probably your > connection manager. Are you running some sort of connection pooling > perhaps? Yes a jdbc pool. There is another web application that uses the same implementation of the pool (albeit a different instance of it) and connections within it are in idle state as you would expect. So the connection pool implementation itself appears to have an alibi. @Johnf > In my case it was caused by a select statement that I neither issued a > commit or rollback. So you have started a transaction without closing it > somewhere in your app. My app continued to work without apparent issue. > The only thing I noticed was pgAdmin3 could not make changes to the data > structure without closing my app. > > So somewhere in your code you have started a transaction without closing > it. So there is no imminent danger then of catastrophic failure such as memory full or disk full or cpu spikes or db slowness given my application only does queries? I'm trying to figure out whether I should chill this weekend on the beach or kiss that good-bye and work this issue? :-) I am setting auto-commit to on, so shouldn't a commit be issued once the select executes (or a rollback on a sql exception)? I guess I'm not 100% sure of txn semantics with the postgres driver that I have. Do you think that despite setting the auto commit mode to off, I still need to issue explicit commits or rollbacks? -----Original Message----- From: Bill Moran [mailto:wmoran@xxxxxxxxxxxxxxxxx] Sent: Friday, July 17, 2009 10:44 AM To: Sharma, Sid Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Idle in transaction In response to "Sharma, Sid" <ssharma@xxxxxxx>: > > 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
