Обсуждение: log_min_duration_statement oddity
Hi all, Postgres version: 8.0.3 I wonder if I understood correctly what log_min_duration_statement does... I set it to 2000, and the result is that all queries running more than 2 seconds on _local_ connections are logged, but long running queries on remote connections are not logged. Is this something to be expected, or it's a bug ? Thanks, Csaba. The complete log relevant configuration as I have it in postgresql.conf: # - When to Log - #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, notice, warning, error log_min_messages = info # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic log_error_verbosity = default # terse, default, or verbose messages #log_min_error_statement = panic # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) log_min_duration_statement = 2000 # -1 is disabled, in milliseconds. #silent_mode = false # DO NOT USE without syslog or redirect_stderr # - What to Log - debug_print_parse = false debug_print_rewritten = false debug_print_plan = false debug_pretty_print = false #log_connections = false #log_disconnections = false log_duration = true log_line_prefix = '%d:%p:%r:%t:' # e.g. '<%u%%%d> ' # %u=user name %d=database name # %r=remote host and port # %p=PID %t=timestamp %i=command tag # %c=session id %l=session line number # %s=session start timestamp %x=transaction id # %q=stop here in non-session processes # %%='%' log_statement = 'none' # none, mod, ddl, all #log_statement = 'all' #log_hostname = false
Csaba Nagy <nagy@ecircle-ag.com> writes: > I wonder if I understood correctly what log_min_duration_statement > does... I set it to 2000, and the result is that all queries running > more than 2 seconds on _local_ connections are logged, but long running > queries on remote connections are not logged. I can't reproduce that. Sure you've diagnosed the problem correctly? It seems highly unlikely that the source of the connection would have anything to do with it. User or database could (eg, perhaps there's a user-specific setting of log_min_duration_statement). regards, tom lane
Well, I'm completely sure that long running queries from local connections are logged, as I can see them in the log file. Quick queries are not logged even for local connections, I tested that too... And I'm also sure that I do have long running queries on remote connections, our application logged some really long running ones. Now the remote connections are coming from Java (the JDBC driver), and I guess it could be that java is setting log_min_duration_statement - I was not aware that this is possible. It would be strange though, why would the JDBC driver set something like this ? Or user specific setting means something completely different ? (I confess I have no idea how can you make user specific settings, or data base specific settings for that matter). In any case, I did not deliberately make any separate settings per data base or per user, and nobody else is messing with postgres settings here... Thanks, Csaba. On Wed, 2005-10-19 at 17:46, Tom Lane wrote: > Csaba Nagy <nagy@ecircle-ag.com> writes: > > I wonder if I understood correctly what log_min_duration_statement > > does... I set it to 2000, and the result is that all queries running > > more than 2 seconds on _local_ connections are logged, but long running > > queries on remote connections are not logged. > > I can't reproduce that. Sure you've diagnosed the problem correctly? > It seems highly unlikely that the source of the connection would have > anything to do with it. User or database could (eg, perhaps there's > a user-specific setting of log_min_duration_statement). > > regards, tom lane
Csaba Nagy <nagy@ecircle-ag.com> writes: > Now the remote connections are coming from Java (the JDBC driver), Oh, there's your problem. 8.0 doesn't have very good support for logging the extended-query protocol, which is what recent versions of the JDBC driver like to use. 8.1 will be better I believe. If you're desperate to get some info, you could force the driver to fall back to V2 frontend protocol. See the archives for details. regards, tom lane
Thanks Tom, now at least I can stop chasing what I'm doing wrong :-) BTW, will be a way to also log the parameter values for prepared statements ? While debugging performance problems it would be invaluable, in many cases it would help me reproduce the problem when only SOME values cause problems. Thanks, Csaba. On Wed, 2005-10-19 at 20:22, Tom Lane wrote: > Csaba Nagy <nagy@ecircle-ag.com> writes: > > Now the remote connections are coming from Java (the JDBC driver), > > Oh, there's your problem. 8.0 doesn't have very good support for > logging the extended-query protocol, which is what recent versions > of the JDBC driver like to use. 8.1 will be better I believe. > > If you're desperate to get some info, you could force the driver to > fall back to V2 frontend protocol. See the archives for details. > > regards, tom lane
Csaba Nagy wrote: > Thanks Tom, now at least I can stop chasing what I'm doing wrong :-) > > BTW, will be a way to also log the parameter values for prepared > statements ? While debugging performance problems it would be You mean the BIND values? No, not in 8.1, but it is on our TODO list now. Prepared logging is better in 8.1, though. -- 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, Pennsylvania 19073