Thread: Re: [PATCHES] WIP patch - INSERT-able log statements

On Mon, 19 Feb 2007, Guillaume Smet wrote:

> Why not simply put something like %log_table% in the sql file and let 
> the admin replace it with sed or whatever he likes?

This is a reasonable approach.  I would suggest that no special characters 
be used though, so that the SQL could be used as-is by a DBA who doesn't 
even know about or want to use tools like sed.  I also think the default 
should be a pg_ name like the pg_log I suggested, partly because I'd like 
this to be completely internal one day--just push the logs into the 
database directly without even passing through an external file first. 
Also, something like pg_log is unlikely to cause a conflict with existing 
tables.  I would bet there's already databases out there with tables 
called log_table, for example, but everyone already avoids naming 
application tables starting with pg_.

A workable syntax might be

INSERT INTO "pg_log" ...

The redundant quotation marks will make it easier to do a search/replace 
to change the table name without worrying about accidentally impacting the 
text of the message, so that even people who aren't aware how to build a 
regular expression that only modifies the first match will probably be OK.

I consider using the same name as the default log directory helpful, but 
would understand that others might consider it confusing to overload the 
name like that.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith@gregsmith.com> writes:
> A workable syntax might be
> INSERT INTO "pg_log" ...

Why is this still under discussion?  I thought we'd agreed that COPY
format was the way to go.
        regards, tom lane

On Mon, 19 Feb 2007, Tom Lane wrote:

> Why is this still under discussion?  I thought we'd agreed that COPY
> format was the way to go.

Joshua Drake said "COPY would be a good option, but INSERT is probably 
what I would use as the default. The most use I see for this is something 
where I am tailing out the log and inserting live into a log db..." and I 
completely agreed with him--that's also how all the applications I'd like 
to build around this feature are expected to operate.  No one said 
anything else on this topic to defend COPY as the right choice until you 
just brought it back up here.

The arguments for COPY are performance and that you don't need to specify 
the table name.  INSERT is slower and you need a name, but it's easier to 
build a UNIX tool style pipeline to import it in real-time.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith@gregsmith.com> writes:
> The arguments for COPY are performance and that you don't need to specify 
> the table name.  INSERT is slower and you need a name, but it's easier to 
> build a UNIX tool style pipeline to import it in real-time.

I can't believe that any production situation could tolerate the
overhead of one-commit-per-log-line.  So a realistic tool for this
is going to have to be able to wrap blocks of maybe 100 or 1000 or so
log lines with BEGIN/COMMIT, and that is exactly as difficult as
wrapping them with a COPY command.  Thus, I disbelieve your argument.
We should not be designing this around an assumed use-case that will
only work for toy installations.
        regards, tom lane

On Tue, 20 Feb 2007, Tom Lane wrote:

> I can't believe that any production situation could tolerate the
> overhead of one-commit-per-log-line.

There aren't that many log lines, and a production environment with lots 
of commit throughput won't even notice.  The installation I work on tuning 
does 300 small commits per second on a bad day.  I can barely measure the 
overhead of whether or not the log files are involved in that if I'm 
importing them at the same time.  The situation obviously changes if 
you're logging per-query level detail.

> So a realistic tool for this is going to have to be able to wrap blocks 
> of maybe 100 or 1000 or so log lines with BEGIN/COMMIT, and that is 
> exactly as difficult as wrapping them with a COPY command.  Thus, I 
> disbelieve your argument. We should not be designing this around an 
> assumed use-case that will only work for toy installations.

Wrapping the commits in blocks to lower overhead is appropriate for toy 
installations, and probably medium sized ones too.  Serious installations, 
with battery-backed cache writes and similar commit throughput enhancing 
hardware, can commit a low-volume stream like the logs whenever they 
please.  That's the environment my use-case comes from.

Anyway, it doesn't really matter; I can build a tool with COPY style 
output as well, it just won't be trivial like the INSERT one would be. 
My reasons for "would slightly prefer INSERT" clearly aren't strong enough 
to override the issues you bring up with the average case.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD