logging as inserts

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