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@xxxxxxxxx> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment:
signature.asc
Description: Digital signature
