> > > > Thanks again. I am wondering as to why the state changes to "Transaction in > > idle" when a query is executed. It'll be nice if that happens only when > > a real change is made (transaction starts) to the database and not when > > a select query occurs. > > This makes no sense. A select query is also a query affected by > transactions. In the example above, if you're in a transaction started > three hours ago, a SELECT will be looking at a version of the database > as it was three hours ago. Also, select queries can change the database > also. Consider nextval() for example. > > The real question is, why are you keeping the transactions open? If > they don't need to be, just commit them when you go idle and everything > can be cleaned up normally. > I am not keeping transactions open. Anytime an insert/delete/update is performed, the change is immediately committed (or rolled back). It is when selects are done that is causing a problem. The flow may be as below: insert into table ....; commit; <idle> (autovacuum can remove dead rows) <some time elapses> delete table ....; commit; <idle> (autovacuum can remove dead rows) select * from ....; read rows from result set <Idle in transaction> (autovacuum cannot remove dead rows) <LONG time elapses> (autovacuum cannot remove dead rows) . . The last select operation is the one of concern. I was just raising the point that select by itself (like the one here) probably shouldn't put the connection in "Idle in transaction" mode. Since my app does not do a commit (or rollback) after every select (and selects in my app don't modify the database), the connection is left in "Idle in transaction" state for several hours until a next insert/update/delete followed by commit takes it to "idle" state. And, autovacuum is unable to remove the dead rows until connection goes to "idle" state. Perhaps, the solution is that I should modify my app to do a rollback followed by every select. But that is a little awkward because selects don't really modify the database in my case. Thanks for your suggestions, sundar. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
