Re: Feature request -- Log Database Name - Mailing list pgsql-hackers

I was thinking of outputing CREATE TABLE at the start of the log file.

I see what you mean that the schemas could be different, so we would
have to output the relevant fields all the time, like timestamp and
username, but because the username would be joined, you would only
output it on connection start, and not for each output line.

---------------------------------------------------------------------------

Andrew Dunstan wrote:
> 
> That assumes we know what the shape of the log tables will be, but this 
> isn't quite clear to me - I can imagine it being different for different 
> needs.  Having an external program to parse the logs into INSERT 
> statements would not be hard, anyway, so I'm not sure that this would 
> buy us much. I'll think about it more. In any case, it should be done in 
> stages, I think, with the first stage simply being what we do now with 
> the optional dbname field added.
> 
> cheers
> 
> andrew
> 
> Bruce Momjian wrote:
> 
> >One idea would be to output log information as INSERT statements, so we
> >could log connection/dbname/username to one table, and per-session
> >information to another table, and server-level info in a third table.
> >
> >If you want to analyze the logs, you could load the data into a database
> >via inserts, and even do joins and analyze the output using SQL!
> >
> >This would solve the problem of failed transactions exporting
> >information, would not be extra overhead for every log message, and
> >would handle the problem of analyzing the log tables while the system
> >was running and continuing to emit more log output.
> >
> >---------------------------------------------------------------------------
> >
> >Andrew Dunstan wrote:
> >  
> >
> >>There seem to be 2 orthogonal issues here - in effect how to log and 
> >>where to log. I had a brief look and providing an option to log the 
> >>dbname where appropriate seems to be quite easy - unless someone else is 
> >>already doing it I will look at it on the weekend. Assuming that were 
> >>done you could split the log based on dbname.
> >>
> >>For the reasons Tom gives, logging to a table looks much harder and 
> >>possibly undesirable - I would normally want my log table(s) in a 
> >>different database, possibly even on a different machine, from my 
> >>production transactional database. However, an ISP might want to provide 
> >>the logs for each client in their designated db. It therefore seems to 
> >>me far more sensible to do load logs into tables out of band as Tom 
> >>suggests, possibly with some helper tools in contrib to parse the logs, 
> >>or even to load them in more or less real time (many tools exist to do 
> >>this sort of thing for web logs, so it is hardly rocket science - 
> >>classic case for a perl script ;-).
> >>
> >>cheers
> >>
> >>andrew
> >>
> >>
> >>    
> >>
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073