Re: Logging pg_autovacuum

On Friday 28 April 2006 12:09, Larry Rosenman wrote:
> Larry Rosenman wrote:
> > Simon Riggs wrote:
> >> On Thu, 2006-04-27 at 14:53 -0400, Tom Lane wrote:
> >>> "Larry Rosenman" <lrosenman(at)pervasive(dot)com> writes:
> >>>> I'd like to see a more concrete definition of what we
> >>>> want Autovacuum to output and at what levels.
> >>>
> >>> autovacuum_verbosity
> >>
> >> Should we call it autovacuum_messages?
> >>
> >> In current usage...
> >>
> >> _verbosity controls how much information each message gives
> >> _messages controls what types of messages are logged
> >
> > That probably works, but I'm not sure about the one to add the VERBOSE
> > to the
> > VACUUM commands autovacuum.c emits.
>
> does the following options satisfy everyone:
>
> autovacuum_messages=
> none silent (nothing output at LOG level)
> database we'd output a LOG message processing database <name>
> table we'd output a LOG message for each table we actually vacuum /
> analyze
> verbose we'd add the verbose flag to each command
>
> the lower levels would include the upper (I.E. verbose implies table +
> database).
>
> If this is acceptable, I'm going to start working on the code to
> implement it.
>

This would certainly be an improvement, but in the intrest of full discussion,
I want to toss out a couple of ideas (they are only partially thought out,
but I think could be useful)

The first is to add a column(s) to pg_class to hold last vaccum/analyze time
for each table. The upsides would be that this puts the information in a
readily accessable place that can be viewed from third party tools and
queried against for easier management along with accomplishing what the
current logging is giving you.

The second is to add a "verbosity level" to pg_autovacuum for each table, to
allow admins to configure specific tables for a more verbose logging. This
way if you have a perticular table that needs additional logging, this could
allow you to have only its vacuums emmitied at whichever log level seemed
appropriate.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL