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