Re: vacuum vs open transactions

On Thu, Jan 13, 2005 at 08:44:56AM -0700, Ed L. wrote:
> On Wednesday January 12 2005 11:30, Michael Fuhr wrote:
> >
> > I'm not aware of a way to find out when a transaction started, but
> > if you have stats_command_string enabled then you can query
> > pg_stat_activity to see when a session's current query started.
>
> Yes, I see that in 7.4 (not in 7.3).  But my purpose would be to remotely
> identify long-open transactions that are causing table bloat by making
> vacuum fail to reclaim space, so it seems I need the transaction start
> time, not query start time.  Most likely this situation occurs when 1)
> someone starts a transaction in psql and then leaves it there, or 2) an
> application opens a transaction prior to getting user input.

For idle transactions pg_stat_activity shows "<IDLE> in transaction"
and the query_start column shows when the transaction became idle
(i.e., when the last statement completed).  So if long-lived idle
transactions are the problem, then at least you can find out how
long they've been idle.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/