Обсуждение: 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