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