Re: [HACKERS] RFC: Industrial-strength logging (long message) - Mailing list pgsql-hackers

Tom Lane wrote:
> 
> Tim Holloway <mtsinc@southeast.net> writes:
> > Request For Comments:  Towards an industrial-strength
> > logging facility
> 
> Sounds pretty good overall.
> 
> > This depends. I like a console-style listing, as my needs are
> > simple. Others would prefer that the log be itself a database.
> 
> Note that logging into a table is harder than you might think.
> For example: (1) The postmaster cannot touch tables at all, so
> no events detected in the postmaster could be logged that way.
> (2) No events detected during a transaction that ultimately
> aborts will be successfully logged.  (3) Logging events such as
> "server failure" would be quite risky --- if the server has
> suffered internal state corruption then it could manage to
> corrupt the log table entirely while it's trying to add its
> last-dying-breath report.
> 
> Fortunately none of these problems apply to stderr, syslog,
> or plain-text-file reporting channels.

Thanks, Tom! I'l file this collection of wisdom to help keep
me on the straight and
narrow. I guess I should have mentioned - at least in its
initial incarnation, cowardice
forbids me to attempt reading or writing PostgreSQL tables
directly. The logfile design is
designed to be text and customizable. If one of those custom
formats just happens to look
like loadable data, well..... :) 

BTW, cowardice also forbids me to attempt message filtering
except by message ID or severity
just yet (no "log all requests from Clevleand to channel 2"
stuff). I will try to provide a
stub for the adventurous, though. For everyone else, there's
Perl.

> > There are 3 types of information in the logging
> > configuration file (which may, but likely won't, be part of
> > pg_hba.conf)
> 
> No, it should definitely not be part of pg_hba.conf, it should
> be a separate configuration file.  (pg_hba.conf has a syntax too
> simple to be easily extensible.)

Of more concern to me was that I THINK I saw pg_hba.conf
being rescanned whenever security
was tested. I don't want to slow that down with a lot of
"one-time" (see below) data.
> 
> Another possibility is to keep the config info in a system table, but
> that would have a number of drawbacks (the postmaster cannot read
> tables, nor can a backend that's just starting up and hasn't finished
> initialization).  On the whole, a plain text file in the database
> directory is probably the best bet.

I think so too -- you just reinforced my feelings. There's
no intrinsic
benefit, since the error messages and channel definition get
compiled
into memory, anyhow.
> 
> > Logging info is read at startup. There may
> > exist signals that cause it to be reread, but not just yet.
> 
> There MUST exist a way to alter the logging level on-the-fly;
> IMHO this is a rock bottom, non negotiable requirement.
> A production system can't restart the postmaster just to tweak
> the logging level up or down for investigation of a problem.

OK, I'm convinced!
> 
> Whether it's a signal or something else remains to be determined.
> We have pretty nearly used up all the available signal numbers :-(.
> I suppose that whichever signal is currently used to trigger
> rereading of the pg_options configuration file could also trigger
> re-reading of the logging config file.

How about via psql or other facilities passing a message
packet?
Can you think of any cases where this would fail? BETTER
YET!
Is there any reason whay pg_options cannot be extended? It
seems like
a natural fit to me - the only reason I didn't suggest it
originally was that
it's been so low-key, I forgot it was there!

> 
> > To avoid potential loss
> > of critical info, any message not explicitly routed at least
> > once gets reported on the default channel - stderr/syslog,
> > unless otherwise configured.
> 
> Hmm, so I'd have to explicitly discard every message I didn't want to
> hear about?  I think that "forced display" like this should only happen
> for high-severity messages, not for routine junk.  There doesn't seem to
> be any notion of message importance in your design, but I think there
> should be.  Most people would probably prefer to filter on an importance
> level, only occasionally resorting to calling out specific message types.

Good point, but it was the second item on the message
override line:

101 INFO "Server started"    A
-----+

The intent was actually more to ensure that if a new release
added new messages, they
wouldn't suddenly pop up in places they'd cause the receptor
to get indigestion (e.g. table loader)
or have critical messages get lost entirely. I did the
pseudocode for lossless message routing
today -- adding a dropout threshold doesn't look like a
major problem.
> 
> > Especially of interest is what the shape of the config file should be.
> > Is the the pseudo-HTML format shown good?
> 
> You could do worse than to borrow BIND's syntax for log control.

*I* like it (I must - I stole almost everything else from
there!). That's what I meant
by "is a "C" format good?". It works well as an extension to
pg_options. Just wanted to see what
others would be most comfortable with.

Again, thanks! This is a big help!
  Tim Holloway