On Thursday January 13 2005 11:37, Michael Fuhr wrote: > > That'll show which transaction is oldest but not how long it's been > open or idle, i.e., whether it's "long-open" or not. I assumed, > perhaps incorrectly, that he was already looking at pg_locks and > wanted to find out which of those transactions had been open for a > long time. Since pg_locks has a pid column, you can join (visually > or via a join query) with pg_stat_activity's procpid column.
What I'm after is a simple way to automatically tell via cron query if there is a transaction staying open long enough (probably an hour) to cause bloat but that doesn't require any visual inspection. While not fool-proof, pg_stat_activity.query_start looks like a 90% answer. It'd be nice if pg_stat_activity.transaction_start were added in the future for a 100% answer, but I'm not sure there's much interest in this apart from our needs. Ed ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
