Re: WIP patch - INSERT-able log statements

On Sun, 18 Feb 2007 18:25, Greg Smith wrote:
> On Sat, 17 Feb 2007, FAST PostgreSQL wrote:
> > #log_output_type = 'text' #Valid values are 'SQL' or 'text'
> > Defaults to 'text' which is status quo. If it is set to 'SQL' log will
> > be output as INSERT commands.
>
> This assumes someone wants either the INSERT-able logs or the current,
> easily readable ones.  I know I don't want either--I want both.  There are
> times I want to look through the logs with a text editor, there are times
> where I want to query against them.
>
> I would suggest treating this similarly to how the Windows eventlog is
> handled:  made SQL INSERT format another option available to
> log_destination, so it can be combined with the existing formats.  In

I think adding the 'format' of the log as an option in the 'destination' may
be confusing.  We can have a new boolean variable like 'output_sql_log' or
'log_sql_format' which will trigger the output of INSERT-able log in addition
to syslog/stderr/eventlog in text format as it is now.

> addition to the syslog concerns you already mentioned (which are
> themselves a showstopper for using this feature in companies that rely on
> or aggregate syslogs), I know I'd want to keep the existing logs rolling
> in parallel while I tested out the SQL-based version for a while, before
> cutting over to exclusively INSERT format logs.
>
> I've thought a bit about how to implement this TODO already (I have a log

Any thoughts on how to output the duration in the same INSERT as that of the
query without having to output two entries per statement as it is done in
text now? ?

> file parser and I hate maintaining it), and the only thing that made sense
> to me was giving a new parameter with the filename to output to in this
> format.  For example, make a new log_sql_filename with the same syntax
> already used for log_filename.  There will probably need to be a second
> parameter for the table name to insert into as you've already commented
> on.  And like Joshua has already suggested, the main useful applications

This would mean that the user will have to maintain two sets of variable. One
for text output (if redirect stderr is on) and another for sql output. My
preference would be for the sql-logs to use the same variables as that of
redirect_stderr. In the directory mentioned by the user on log_directory we
just output the sql logs in a file with an '.SQL' appended to the filename
specified by the user in log_filename. This also means we can make use of the
log_truncation and log_rotate options (I hope. I will have a look at that).
Of course we document this making user aware of this. (The other option is we
output the logs in a subdirectory called 'SQL' which we can silently create,
if we are allowed to ! ! !)

This also means only two new variable is created in postgresql.conf for this
feature. To trigger the output and the table name. (Or we can introduce only
the table name variable which will automatically trigger the SQL log output.
Maybe confusing ? ? )

Rgds,
Arul Shaji


> for this feature I've thought of all involve reading from the INSERT-able
> logs in real-time, using something like "tail -f", and pumping that data
> immediately into a logger table.
>
> Also, I feel that supporting the whole log_line_prefix syntax for this
> feature is not just overkill, it's a bad idea.  Output everything in a
> standard, complete format instead, and then it becomes easy for the
> community at large to build tools on top of that to analyze the log
> database entries instead of having so many ad-hoc approaches.  You want a
> subset, use a view or copy just the fields you want into another table.
> I would guess this simplifies the patch as well.


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