Re: pg_stat_activity xact_start and autovacuum

On Feb 11, 2008 2:27 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Dawid Kuroczko escribió:
> > I'm using 8.3.0 and I see that autovacuum processes in
> > pg_stat_activity have xact_start.
> >
> > As far as I know, since at least 8.2.x the VACUUM does not start a new
> > transaction.
> > If that statement is correct, the xact_start column in
> > pg_stat_activity should be NULL...
> > Why does it matter? Monitoring. It's good to know the age of oldest
> > running transaction, and autovacuuming is well, adding noise.
> Autovacuum certainly uses transactions ... ??

I am referrring to the E.8.3.5 Release 8.2 Release Notes:

* Allow VACUUM to expire rows without being affected by other
concurrent VACUUM operations (Hannu Krossing, Alvaro, Tom)

I have probably oversimplifed my statement above. What I am monitoring
is the age of the oldest transaction, to be alerted before tables accumulate
too many dead rows. From this point of view long running VACUUM is not
a problem (since relese 8.2).

Right now I am using:
SELECT extract('epoch' from min(xact_start)) AS oldest_xact_age
FROM pg_stat_activity
WHERE current_query NOT LIKE 'autovacuum:%';

...which works fine but somehow I feel that if xact_age would be NULL, it would
ring more true. Since VACUUM does not prevent VACUUMING it can take
days to complete and still I wouldn't need to worry. ;-)

Let me know if I mixed things up horribly. :-)

Regards,
Dawid