Re: pg_stat_activity xact_start and autovacuum

Dawid Kuroczko escribió:

> > 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.

> 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)

Oh, I see. Well, it is certainly running in a transaction, even though
that transaction does not prevent other vacuums from removing old rows.

> 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. ;-)

Actually it's not just autovacuum; it's any lazy vacuum. It's hard to
tell those processes apart in pg_stat_activity. Perhaps we could have
added a column in pg_stat_activity indicating processes that don't hold
old tuples, but I feel that would have been a little too much.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.