Re: autovacuum "connections" are hidden

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Tom Lane wrote:
> >>> Hmm, autovacuum won't show up in pg_stat_activity because it never
> >>> calls pgstat_report_activity(). Seems like maybe it should, though.
>
> > Yep. I'll fix it. Should I backpatch to 8.1?
>
> Yeah, probably, because this is really a regression from 8.0: the old
> contrib autovacuum's activities would show in pg_stat_activity, since
> it was just issuing the VACUUM/ANALYZE commands normally.

Hmm. I had assumed this would be a quickie, but it turned out not to be
the case. The problem is that while we can make autovac report quite
easily, other backends will ignore the backend entry because it hasn't
set a database ID. (In pg_stat_get_backend_dbid, entries with NULL dbid
return NULL, so the pg_stat_activity view doesn't show them because it
uses a inner join. Also, pgstat_report_autovac sets the database entry,
but it doesn't do anything about the backend entry.)

The problem is that pgstat_bestart (called in InitPostgres, which
autovac calls) deliberately ignores autovacuum, due to not having a
client address. We could create a fake client address (which doesn't
seem easy to do), or we could change pg_stat_activity to use an OUTER
JOIN. Not sure what other options are there.

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