Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Logging statements and parameter values

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
Cc: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>,postgres hackers <pgsql-hackers(at)postgresql(dot)org>,Ted Powell <ted(at)psg(dot)com>, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Subject: Re: [HACKERS] Logging statements and parameter values
Date: 2006-02-06 13:40:37
Message-ID: 1139233237.1258.5.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Mon, 2006-02-06 at 13:28 +0100, Csaba Nagy wrote:

> For me the usage pattern would be: log all params, bind time values, on
> the same log line as "log_min_duration" entries. That's what I need to
> know which are the non-performant queries, and it also helps on
> occasions to identify application problems. 

You remind me that two sets of parameters could be logged

Trouble is, you need to know both
- parameters that were used to plan the query
- parameters used for this execution of the query
since very often it is the combination that is the problem. i.e. it was
a good query at plan time and if re-planned would also be a good query,
but running the earlier plan with the later set of parameters is bad.

Perhaps it would be useful to store the parameters that were used to
plan the query with the portal, so we could have an option to say "and
with what parameters was this query planned". That would then sensibly
appear on the log_min_messages log line, as you suggest. This is
important for diagnosing many run-time issues accurately.

Maybe we should expand the list to
log_parameters = none | bind | plan | exec | all

bind = log parameters directly at bind time, using a separate log line;
do not store them. Useful mainly as an interface debugging aid.

plan = store parameters used for planning with portal, then output them
with the statement at log_min_message time 
e.g. (plan parms: p1= p2= ...) - which is very similar to what we do now
with spitting out the SQL statement since that is not resent for each
execution

exec = store the parameters used at bind time and output them with the
statement e.g. (exec parms: p1= p2=)

all = store the parameters used at bind time and output them with the
statement, as well as the exec parms e.g. (plan parms: p1= p2= ...)(exec
parms: p1= p2=)

none = nada, not even the current log lines for bind

Sounds too much, but you don't really want all of that all of the time.

Best Regards, Simon Riggs



In response to

pgsql-hackers by date

Next:From: Richard HillsDate: 2006-02-06 13:42:55
Subject: Re: Shared memory and memory context question
Previous:From: Csaba NagyDate: 2006-02-06 12:28:13
Subject: Re: [HACKERS] Logging statements and parameter values

pgsql-general by date

Next:From: Bruno Wolff IIIDate: 2006-02-06 14:32:50
Subject: Re: change current user in pl/pgsql function
Previous:From: Sergey KarinDate: 2006-02-06 12:43:30
Subject: change current user in pl/pgsql function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group