On Tue, Aug 01, 2006 at 11:48:04AM -0700, Sundar Narayanaswamy wrote:
> 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 ?
You'll have to check the docs, but it basically comes down to that
VACUUM can only remove rows that are older than the oldest transaction.
Whether this is per database or per cluster, I'm not sure...
It's not that "IDLE in transaction" is bad in itself. It's that if you
started a transaction three hours ago, no tuples deleted in the last
three hours can be truly removed because that transaction can see them.
> 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.
You have to set "stats_command_string=on" in the server config. But the
output from "ps" is good also.
>
> 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.
hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
