From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | "Ed L(dot)" <pgsql(at)bluepolka(dot)net> |
Cc: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: vacuum vs open transactions |
Date: | 2005-01-13 17:09:41 |
Message-ID: | 20050113170941.GC80786@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | William Yu | 2005-01-13 17:13:42 | Re: pgsqlODBC problems |
Previous Message | Doug McNaught | 2005-01-13 17:02:37 | Re: Adding UNIQUE constraint on NULL column |