Thread: logging as inserts
Hello, I am looking at having one of our guys write up the code to allow logging as insert statements. I have a couple of questions. What would we like the postgresql.conf option to be? I was thinking log_statements_as_inserts = (t/f) However I thought that was getting a little silly. Also do we want to allow logging as inserts for all options? There is a lot of different types of logging we can do. Sincerely, Joshua D. Drake -- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564 Custom programming, 24x7 support, managed services, and hosting Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG Reliable replication, Mammoth Replicator - http://www.commandprompt.com/
On Tue, 1 Mar 2005, Joshua D. Drake wrote: > Hello, > > I am looking at having one of our guys write up the code to allow > logging as insert statements. I have a couple of questions. > > What would we like the postgresql.conf option to be? I was thinking > log_statements_as_inserts = (t/f) > > However I thought that was getting a little silly. Also do we want > to allow logging as inserts for all options? There is a lot of different > types of logging we can do. Seems to me that a better approach is a script which, given the log file format, is able to parse and allow the user to format the insert themselves. The reason I say this is that users are almost always going to want a human readable log. As such, post processing the log outside of the database system seems to make sense. > > Sincerely, > > Joshua D. Drake Gavin
On Tuesday 01 March 2005 18:16, Gavin Sherry wrote:
> On Tue, 1 Mar 2005, Joshua D. Drake wrote:
> > Hello,
> >
> > I am looking at having one of our guys write up the code to allow
> > logging as insert statements. I have a couple of questions.
> >
> > What would we like the postgresql.conf option to be? I was thinking
> > log_statements_as_inserts = (t/f)
> >
> > However I thought that was getting a little silly. Also do we want
> > to allow logging as inserts for all options? There is a lot of different
> > types of logging we can do.
>
> Seems to me that a better approach is a script which, given the log file
> format, is able to parse and allow the user to format the insert
> themselves.
>
Doesn't the Practical Query Analysis project already do this? (It's up on
pgfoundry for anyone interested)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> Seems to me that a better approach is a script which, given the log file > format, is able to parse and allow the user to format the insert > themselves. Seems like a cool functionality that could be added to PQA... Chris
Gavin Sherry wrote: > On Tue, 1 Mar 2005, Joshua D. Drake wrote: > > >>Hello, >> >>I am looking at having one of our guys write up the code to allow >>logging as insert statements. I have a couple of questions. >> >>What would we like the postgresql.conf option to be? I was thinking >>log_statements_as_inserts = (t/f) >> >>However I thought that was getting a little silly. Also do we want >>to allow logging as inserts for all options? There is a lot of different >>types of logging we can do. > > > Seems to me that a better approach is a script which, given the log file > format, is able to parse and allow the user to format the insert > themselves. > > The reason I say this is that users are almost always going to want a > human readable log. As such, post processing the log outside of the > database system seems to make sense. The problem with the current format is that it's really hard to parse, esp. since log_line_prefix can be freely defined. We just received a complaint about pgAdmin's log display mechanism, where the reading of the logfile over a connection will lead to noise in the logfile from the pgAdmin queries with log_statement=all. Reading the logfiles in a different way doesn't make things much better: the underlying problem is that for client side diagnosis in production systems it should be possible to tap only *some* backends with full log information (including duration, io statistics etc) while other traffic is ignored. I had to do this quite often, and using a logfile which logs all traffic won't make you happy for that purpose. If all logging would go to a table which has appropriate columns to restrict the result set to investigate, this could help. An important note: the log column should contain the *complete* query, not just a truncated one (as we have from stats_command_string) caused by some IPC limitation. Regards, Andreas
Josh, > >>I am looking at having one of our guys write up the code to allow > >>logging as insert statements. I have a couple of questions. > >> > >>What would we like the postgresql.conf option to be? I was thinking > >>log_statements_as_inserts = (t/f) Nope. log_destination = 'inserts' #not a new GUC! insert_columns = '%u,%d,%r,%p, ... %$' #this new GUC would define a list of comma-seperated columns as escape codes defined via the same code set as log_line_prefix. The only change would be the addition of %$, which would symbolize the "statement" being logged. I'd also assert that this option should log the inserts to a "stderr" and thus take advantage of all of the redirection, rotation, etc that we now support for stderr logging. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> What would we like the postgresql.conf option to be? I was thinking
>> log_statements_as_inserts = (t/f)
> Nope.
> log_destination = 'inserts' #not a new GUC!
That seems a bit bizarre to me. The facility isn't a new log
destination; what it is is a different way of formatting what's
sent to the log.
> insert_columns = '%u,%d,%r,%p, ... %$'
> #this new GUC would define a list of comma-seperated columns as escape codes
> defined via the same code set as log_line_prefix. The only change would be
> the addition of %$, which would symbolize the "statement" being logged.
I think what you'd probably really want to write is something like
log_message_format = 'INSERT INTO mytab VALUES(%this, %that, ...);'
the point being that otherwise we'll need yet another GUC var to determine
the table name used in the INSERT. With a suitable set of escape codes
we could probably arrange for the existing behavior to correspond
to a particular value of log_message_format, and then there isn't
anything weird going on here; you are just changing away from a default
format.
I wonder whether this could be defined in a way that lets it replace
log_line_prefix ... otherwise we have to think about the interaction of
the two facilities.
regards, tom lane
Tom, > That seems a bit bizarre to me. The facility isn't a new log > destination; what it is is a different way of formatting what's > sent to the log. It's not, but it functions like one. And ultimately, the destination *is* someplace different; likely the DBA will be piping the log output to another database somewhere. > log_message_format = 'INSERT INTO mytab VALUES(%this, %that, ...);' Yeah, good idea. > I wonder whether this could be defined in a way that lets it replace > log_line_prefix ... otherwise we have to think about the interaction of > the two facilities. Well, that's why I like the idea of using log_destination. It makes it clear that log_line_prefix doesn't work if log_destination <> 'stderr'. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> I wonder whether this could be defined in a way that lets it replace
>> log_line_prefix ... otherwise we have to think about the interaction of
>> the two facilities.
> Well, that's why I like the idea of using log_destination. It makes it clear
> that log_line_prefix doesn't work if log_destination <> 'stderr'.
But log_line_prefix works fine for all destinations, which is exactly
why this new facility isn't a destination. You're just confusing
matters by wanting to treat it as one.
regards, tom lane
Tom, > But log_line_prefix works fine for all destinations, which is exactly > why this new facility isn't a destination. You're just confusing > matters by wanting to treat it as one. Hmmm ... hey, if we just allowed extra text in log_line_prefix, and allowed %$ to enclose the statement with literal quoting, you could do this all through log_line_prefix, as: log_line_prefix='INSERT INTO pg_log VALUES ( %u, %p, ''%t'', ''%$'')' -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: >Tom, > > > >>But log_line_prefix works fine for all destinations, which is exactly >>why this new facility isn't a destination. You're just confusing >>matters by wanting to treat it as one. >> >> > >Hmmm ... hey, if we just allowed extra text in log_line_prefix, and allowed %$ >to enclose the statement with literal quoting, you could do this all through >log_line_prefix, as: > >log_line_prefix='INSERT INTO pg_log VALUES ( %u, %p, ''%t'', ''%$'')' > > You can have extra text today, apart from the fact that there's no escape for the statement. try it and see. log_line_prefix is (not coincidentally) very similar to a printf-type format string. Indeed, use of admin-supplied fixed text was always intended - see the discussions that led up to it. Incidentally, %$ doesn't tstrike me as ideal - to conform to the way the rest are done we should use an alphabetic character, not $. cheers andrew
Andrew, > Incidentally, the fly in this particular pot of ointment is that we > potentially log a lot more than just statements. Oh, yeah, but just about anything can be put in the "statement" field; errors, disconnects, etc. Hmmm ... though we don't currently apply "log line prefix" to those, do we? > Incidentally, %$ doesn't tstrike me as ideal - to conform to the way the > rest are done we should use an alphabetic character, not $. Sorry, I'm being perlish ;-) -- --Josh Josh Berkus Aglio Database Solutions San Francisco