Re: [HACKERS] Logging - events supported - Mailing list pgsql-hackers

> Following is an updated list of the messages to be channeled by the proposed logging system.
> 
> THESE AND *ONLY* THESE are slated for implementation. If you have items you want
> included, PLEASE LET ME KNOW! As it stands, this is a pretty minimal set.
> 
> Bear in mind that the logger is NOT a debugger. Logged messages are expected to be related to administrative events,
> including, but not limited to - server status (including load-balancing and fault
> reporting), security, user connections, and service requests. Added are the 1xxx class, which is designed to assist
the
> existing debugging system by providing a linkage between the free-form debugging messages and the formalized log
system.
> via the LOGBUG macro, which write to both debugging output AND logging.
> 
> Hint 1: If you are about to emit a plethora of debugging messages and you want a timestamp and/or entry in a log
> database, use LOGBUG and include a unique event ID in the message so that the two can be reconciled.
> 
> Hint 2: If you want to log specific debugging info into a database table, format it as appropriate for a table load
and
> route it to a channel destined for that table. E.g.:
> 
>  sprintf( tracebuffer, "'%l|MEMSHORTAGE'|%d|%d", ++event_id, bytes_used, max_bytes);
>  LOGBUG( 1003, tracebuffer );
> 
> FEEDBACK NEEDED! THANK YOU!
> 
> Logging classes:
> ---------------
> 1xz - The PostgreSQL server
> 2xx - User-related information
> 3xx - Transaction information
> 4xx - EXPLAIN results (???)
> 9xx - General system alerts
> 1000-1999 debugging events
> 
> Right now, the following are considered likely candidates,
> subject to user feedback:
> 
> server info
>  Server name, signal ID
> 101 - Server started
> 102 - Server shutdown
> 103 - Signal xxx received
> 104 - Server ABEND              ^^^^^

This reminds too much the old IBM dinosaurs. Maybe `crash' is more modern.


> user session
>  userid, port or terminal ID, authentication scheme name
> (e.g. md5). session ID
> 201 - User xxxx connected via port/terminal xxxxxxxx
> authenticated by aaaaa
> 202 - User xxxx disconnected
> 203 - FORBIDDEN - connection denied for user xxxx via
> port/terminal xxxxxxxxxx rejected by aaaaaaa
> 
> show commands
>  Session ID, command text
> 301 - SELECT text
> 302 - INSERT text
> 303 - UPDATE text
> 304 - DELETE text


Utility commands? Sequences? Table alteration commands?


> show results
>  session ID, count or OID. primary/first/only table ID
> affected
> 401 - SUCCESS - nnn records retrieved
> 402 - SUCCESS - record inserted at OID
> 403 - SUCCESS - nnn records updated
> 404 - SUCCESS - nnn records deleted
> 405 - FORBIDDEN - action xxxxxx denied to user xxxx on table
> xxxxxxxx
> 
> explain
>  as below:
> 500 EXPLAIN transaction ID sequence cost rows bytes
> 
> miscellaneous
>  explanatory text
> 900 - Logging configuration file "ffff" was not found or
> denied read access. Using default logging.
> 901 - Logging configuration file "ffff" could not be
> processed - invalid text at line nnn.
> 902 - User overrides non-existent message ID nnn
> 903 - Channel requests non-existent message ID nnn
> 904 - end of section starting on line nnn was not found
> 905 - start of section ending on line nnn was not found
> 906 - (message from logging configuration file)
> 
> 1000-1999 - LOGBUG macro 
>  text - message text
> user defines as needed - not standardized
> 
> ************
> 


I suggest also the following things:

1)    each log entry should be a single line. This would greatly simplifythe automatic processing of log files using
standardunix tools,including loading entries into a database table.
 

2)    each entry should be prefixed by a timestamp and the backend pid,more or less like the syslog entries. I suggest
thefollowingformat, which is the one currently implemented by elog_timestamp()
 
991020.14:29:56.699 [7172] started: host=127.0.0.1 user=dz database=dz991020.14:31:02.723 [7172] query: select * from
pg_user

3)    the logging level can be changed on-the-fly by sending a SIGHUP tothe postmaster and then automatically to all
thebackends. Currentlyit reloads the pg_options file, which was originally designed exactlyfor controlling the debug
andlog messages without restarting thepostmaster and all backends, but it could also reload any otherconfiguration
file.

-- 
Massimo Dal Zotto

+----------------------------------------------------------------------+
|  Massimo Dal Zotto               email: dz@cs.unitn.it               |
|  Via Marconi, 141                phone: ++39-0461534251              |
|  38057 Pergine Valsugana (TN)      www: http://www.cs.unitn.it/~dz/  |
|  Italy                             pgp: finger dz@tango.cs.unitn.it  |
+----------------------------------------------------------------------+