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/ ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
