Re: Autovacuum help..

From: Sundar Narayanaswamy <sundar007(at)yahoo(dot)com>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Autovacuum help..
Date: 2006-08-02 14:36:09
Message-ID: 20060802143609.65281.qmail@web32809.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2006-08-02 14:39:51 Re: prepare, execute & oids
Previous Message Joshua D. Drake 2006-08-02 14:33:11 Re: Best Procedural Language?