Re: Autovacuum help..

On Wed, Aug 02, 2006 at 07:36:09AM -0700, Sundar Narayanaswamy wrote:
> 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.

Sorry, selects still advance the transaction counter, create a
snapshot, hold locks, can still fire triggers, update stats, call
external functions, etc. Maybe in your case they don't but maybe
someday you'll make a change to the database that will.

Maybe modify your app so selects arn't run inside an explicit
transaction. Then you don't need to commit or rollback anything.

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

I imagine commit is cheaper and safer than a rollback...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.