Search Postgresql Archives

Re: Autovacuum help..

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux