WIP patch - INSERT-able log statements

Hi,

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

"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 as INSERT commands.

The second variable is of interest. We need to specify a table in the insert command. My preferred option is for the user to give one and he can create it if and when he wants to. The alternative is we decide the table name and make initdb to 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 columns will have NULL. User can still give log_line_prefix in any order he wants, and logger will output it in appropriate columns. 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. In the existing log we can make sense of the duration log by matching it with the statement above it or by the statement which gets 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. (My preferred option is to add another column to the table structure defined above as 'duration'. But haven't figured out how 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 syslog writer function breaks up the log into several lines to accomodate various platforms. Syslog also then adds other information before outputting it, which
cannot be loaded onto a table. The preferred option is to educate the user through documentation that SQL type log output is best served when it is output to stderr and redirected to a file? Same goes with other aspects such as verbose and various 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 text and 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 ordinary user 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 this email 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 email unsubscribe(at)fast(dot)fujitsu(dot)com(dot)au