Tom Lane wrote:
> As of 8.4, I think the above statements are true. In prior releases,
> transactions just advertised the age of their first snapshot, so an
> open transaction created an issue for VACUUM regardless of whether
> it was serializable or not. 8.4 has more bookkeeping that allows a
> transaction to report that it currently has no live snapshots.
>
I've been using this...
pgadmin=# select datname,procpid,usename,now() - xact_start as "Age",
current_query from pg_stat_activity where now()-xact_start> interval '30
min' order by xact_start;
datname | procpid | usename | Age |
current_query
---------+---------+---------+-----------------+------------------------------------------------
lss | 22528 | fds | 11:43:15.715747 | <IDLE> in transaction
lss | 22979 | fds | 11:38:27.101715 | <IDLE> in transaction
lss | 22980 | fds | 11:38:27.101676 | <IDLE> in transaction
lss | 23150 | fds | 11:36:39.850232 | <IDLE> in transaction
lss | 22987 | pgadmin | 03:38:57.974805 | autovacuum: VACUUM
ANALYZE ts.disc_yield_daily
lss | 24272 | pgadmin | 02:37:19.303353 | autovacuum: VACUUM
ANALYZE ts.disc_yield_hour
(6 rows)
to detect old stale transactions and other long running operations
in 8.4, there's a different high water mark I should be using instead?