Re: [HACKERS] Logging statements and parameter values

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