Re: log_statement and syslog severity

On Wed, Mar 10, 2010 at 10:55 AM, Ben Chobot <bench(at)silentmedia(dot)com> wrote:

> On Mar 10, 2010, at 12:15 AM, Stuart Bishop wrote:
>
> > 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).
>
> Have you looked into syslog-ng? I believe it does just this sort of thing
> for you.
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

We use syslog-ng for sorting our syslogs into different log files. It works
well enough.

What annoys me is that postgresql will split a SQL statement across several
syslog "events". I know that syslog itself has a maximum message size and
that this is required for really long SQL. However, I wanted to log each
SQL statement as a single "event". Syslog-ng can direct output to a pipe.
Putting these together I had begun working on a system where out syslog host
(which logs this from lots of other, unrelated systems) would sort the
postgresql logs to STDIN on a perl program that would reassemble the
individual SQL statements and write them out in a different format. I never
completed the project (just got busy with more important things). However,
I thought that this approach was feasible.

That being said, I would love it if Postgresql had logging mechanism
plugin. Maybe it could load a so/dll that would handle logging. That
so/dll would export a function like this:

void pg_log_init (/* some params related to the database instance */);
void pg_log_done (void); // called on database shutdown.

void pg_log_sql (int runtime, int rows, int status, const char *sql,
const char *user);

"status" would be some code to indicate if the sql was successful or not.

The above is just off the top of my head. I've done no real research on if
the above would be sufficient or correct.

Just an idea...