Re: log_statement and syslog severity

2010/3/10 Stuart Bishop <stuart(at)stuartbishop(dot)net>:
>
>
> On Wed, Mar 10, 2010 at 8:51 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>
>> Greg Sabino Mullane wrote:
>>>
>>> Bruce replied:
>>> ...
>>> >> This means that, even using syslog as a destination, it's not possible for
>>> >> me to filter statements without some sort of log-text parsing, which I'd
>>> >> prefer to avoid on effort, performance and data-integrity grounds.
>>>
>>> > Our logging system is very flexible, but not work-free on the user end.
>>> > I don't see us changing things in that area.
>>>
>>> Bruce, that's a little harsh, I think the original poster has a legitimate
>>> request. Personally, I'd love to be able to split the logs on various things,
>>> the most important to me being durations and per-database. I looked at the
>>> code about a year ago to see how hard this would be and found it non-trivial
>>> (for me), as we're really assuming hard things go to a single filehandle.
>>> It's definitely an area for improvement, and should be a TODO if not already.
>>
>> This issue has been discussed and I think the community conclusion was
>> that this should not be done by the database but rather by external
>> tools.  I think I was giving an accurate portrayal of the odds of this
>> getting added.  I do not think there is enough support for this to be a
>> TODO item.
>
> How do you plug in this external tool?
>
> Installing a filter on stderr doesn't play well with packaged versions of PostgreSQL and probably not Windows either. You also don't get easily machine readable output.
>
> It might be possible to trick csvlog to log to a static filename, and perhaps substituting that with a named pipe might work (under unix at least).
>
> syslog doesn't give you easily machine readable output. I'm not sure how syslog implementations handle high load (our sysadmins won't use it, so I haven't investigated this further).
>
> I need to be analyzing log messages from PostgreSQL in real time, so am starting to investigate solutions. It seems painful, which would be avoidable for future generations if PostgreSQL could spawn a subprocess and send log messages to that in a machine readable format. Perhaps useful filters might start to exist and eventually end up in contrib or core.

We already have a subprocess that takes this, and if we use CSV format
it's machine readable.

I had a patch sometime back last autumn that did a fairly major
restructuring to allow some of this kind of refactoring, but it was
rejected (on reasonable grounds). My next thought around that was to
add a "pipe" style log_destination to just make it possible to hand
things off to a different process. The reasonable way to do it would
be to send it out in CSV format. It'll cause a fairly large amount of
parsing overhead and such compared to a builtin solution, but it'll
give the flexibility to develop all such filters outside of core.

But that's all 9.1 material, of course.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/