Re: Autovacuum help.. - Mailing list pgsql-general

> Well, you need to work out why they are unremovable. For example, do
> you have any really long lived open transactions. These are generally a
> bad idea, for all sorts of reasons. If you don't commit occasionally,
> none of your changes will be visible to anyone else.

I have multiple databases on the server and a JDBC connection to
each of the database from my app. Only couple of the databases have continuous
inserts/delete activity and they usually stay in "idle" state.
Other databases, however, don't have much of updates happen,
but mostly selects. As I understand it, when executing the first select
after a commit or rollback, the state changes to "Idle in Transaction"
and state goes back to "idle" state when a commit/rollback happens.
These databases that have selects on them most of the time stay in
the "Idle in Transaction" state most of the time.

Now, in this situation, if some databases stay in "Idle in transaction",
would the dead rows be unremovable from other databases (that are in "idle"
state) as well ? In other words, should ALL of the databases/connections
in the server be in "idle" state for the autovacuum to be able to remove dead
rows in any database ?

>
> > My question is, is autovacuum expected to work in situations like this
> where I
> > have a long lived connection to the database ? After I shutdown my
> application,
>
> It's got nothing to do with connections and everything to do with open
> transactions. I'd suggest you run a:
>
> select * from pg_stat_activity ;
>
> And look for messages like: IDLE in transaction.
>

I tried this, but I see <command string not enabled> as the current query.
I searched for docs on this view (pg_stat_activity), but couldn't find
much. Could you help me to enable it so that I can see the current query
in this view ? I found that some databases are in "idle in transaction" from
the
ps -afe command.

> > why am required to run the "analyze" command before the dead rows are
> removed
> > (autovacuum is not able to remove the dead rows until I run the "analyze"
> > command) ?
>
> The stats arn't updated until the rows are actually removed. Eventually
> autovacuum would have done the analyze for you...
>
> Hope this helps,
>

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.


> Have a nice day,


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com