Re: vacuum vs open transactions

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