OK, It looks like there's at least some interest in having a
true logging facility for the PostgreSQL backend. I've
researched the source code and feel fairly certain that I
can provide a patchset that would provide information useful
to an administrator without having an unfortunate impact on
reliability or performance.
The one thing I do have trouble with is how to present the
data to a standard log analyzer. Does this mean having a
consistent column format, a la Apache? I'm reluctant to do
that because there are 3 different contexts that I see as
reportable, and trying to shoehorn them into a fixed format
is rather awkward.
First, I should mention that I don't plan to go any lower in
detail than the command level. I.e: if a batch update of
4000 rows is applied, I log the fact that the update
occurred, NOT exactly what happened with each record. I
depend (foolishly or no) on the versioning component for
that. Having made that constraint, I see 3 contexts:
1) Server events. E.g.:
[1999-03-31 17:24:32 -0005] postmaster[134] Server started
2) User sessions.
[1999-03-31 19:11:12 -0005] postmaster[134] session[197]
user wonko logged in 172.24.32.11 using md5
[1999-03-31 19:12:14 -0005] postmaster[134] session[198]
user postgres logged in /dev/tty3 using trust
3) Transactions.
[1999-03-31 21:15:57 -0005] postmaster[134] session[198]
user postgres EXECUTE: SELECT * FROM CATCODES WHERE
CAT_MAJOR = '100';
[1999-03-31 21:15:59 -0005] postmaster[134] session[198]
user postgres RESPONSE: SUCCESS - returned 5 rows
[1999-03-31 23:45:57 -0005] postmaster[134] session[197]
user wonko EXECUTE: DELETE FROM CATCODES;
[1999-03-31 21:45:58 -0005] postmaster[134] session[197]
user wonko RESPONSE: FAILED - Access violation
Don't get too excited about the exact format. I've got a
simple but powerful mechanism to allow custom message
formatting for each loggable event, including the ability to
internationalize.
I do want to have a good default format, however.
Comments? Questions? Rude remarks?
Tim Holloway
MTS Associates, Inc.
************
