WIP patch - INSERT-able log statements - Mailing list pgsql-patches

Hi,

I've been working on the following TODO item and attached is an initial patch. (It is only partial and not yet
completelyfunctional) 

"Allow server log information to be output as INSERT statements
This would allow server log information to be easily loaded into a database for analysis. "

I want to confirm, if what I have done so far is what community is looking for and also want to clear some doubts.

What is done so far
---------------

Two postgresql.conf variables

#log_output_type = 'text'        #Valid values are 'SQL' or 'text'
#log_output_table_name = 'auditlogs'

These control how to output the log. Defaults to 'text' which is status quo. If it is set to 'SQL' log will be output
asINSERT commands. 

The second variable is of interest. We need to specify a table in the insert command. My preferred option is for the
userto give one and he can create it if and when he wants to. The alternative is we decide the table name and make
initdbto create one.   

The proposed log output structure
------------------
INSERT INTO user_defined_table values( timestamp_with_milliseconds,  timestamp, username,  databasename, sessionid,
host_and_port,host, proc_id, command_tag,  session_start, transaction_id,  error_severity,  SQL_State_Code,
error_message);

All these columns will follow the current rules of log output. ie, unless explicity requested by the user, these
columnswill have NULL. User can still give log_line_prefix in any order he wants, and logger will output it in
appropriatecolumns. The code has been modified to do  
this.

Issues/Questions are:
- How about 'Statement duration log'.  This will come to the logger as a single string and after the query execution.
Inthe existing log we can make sense of the duration log by matching it with the statement above it or by the statement
whichgets printed besides it (Again as  
a single string). But when this is loaded onto a table doesn't make much sense untless everything is in a single row.
(Mypreferred option is to add another column to the table structure defined above as 'duration'. But haven't figured
outhow to achieve this, because the  
statement is printed first and then the duration as another log.)

- If the SQL log output is to the syslog, then it becomes pretty awkward and possibly useless because our current
syslogwriter function breaks up the log into several lines to accomodate various platforms. Syslog also then adds other
informationbefore outputting it, which  
cannot be loaded onto a table. The preferred option is to educate the user through documentation that SQL type log
outputis best served when it is output to stderr and redirected to a file? Same goes with other aspects such as verbose
andvarious other statistics log.  

- There are also other minor issues such as, the actual query currently gets output in log as 'Statement: CREATE
........'.For sql type log we may not need the 'Statement:' part as it will be in a column ? Do we remove this in both
textand SQL outputs ? 

Rgds,
Arul Shaji

This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the
ordinaryuser of the email address to which it was addressed and may contain copyright and/or legally privileged
information.No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive
thisemail in error, please return to sender. Thank you. 

If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please
emailunsubscribe@fast.fujitsu.com.au