At 15:43 +0200 on 12/10/1999, Oleg Bartunov wrote:
> Cool ! Then we could use standard log-analyzers to produce
> any usage statistics.
Things will have to be considered before this can be done. The main problem
is that there is no way you can delimit a query and be sure the delimiter
is not in it. For example, if you want to put the query into double quotes,
you will easily run into a problem with standard log analyzers if you have
1999-10-12 10:00:37 herouth 8833 123.456.78.90 "CREATE TABLE "myName";"
success ""
There can be several solutions. Ones I can think of off-hand are:
(1) Encode all newlines and double quotes within the query:
1999-10-12 10:00:37 herouth 8833 123.456.78.90 "CREATE TABLE %22myName%22;"
success ""
This method is good for standard log analyzers. Of course the % character
will also have to be encoded. It's not very good if you want to search
(grep?) the file for a specific query, because you will have to encode the
piece you are searching for in order to match.
(2) Format the log in a way proper for Postgres COPY
Separate the fields (timestamp, username, session-id, host ip...) with
tabs, and escape the newlines and tabs in the query. No quotes, naturally.
This method is good for loading a portion of the log into a postgres table
and then querying it to death. After all, what do we have a database system
for? :)
(3) Put the actual statements in a separate file.
Log file entry:
1999-10-12 10:00:37 herouth 8833 123.456.78.90 "CREATE TABLE statement
2345" success ""
Query file entry:
2345
CREATE TABLE "myName";
To find all references to "myName", you search the query file, get the
numbers, and then look in the log to see where the statements were used and
what happened to them.
This makes for shorter log lines, more readability, standard log analysis,
but more work in looking up references to specific tables etc.
While we are at it, we should have a log roll mechanism built into
postgres. There are roll scripts out there, but what happens if you pipe
into a roll program and that program hangs? It may hang Postgres. Thus, it
should have its own rolling mechanism, like a webserver does.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
************
