Re: prepared statements don't log arguments? - Mailing list pgsql-hackers

> Oliver Jowett <oliver@opencloud.com> writes:
> > Simon Riggs wrote:
> >> OK, thats what I hoped you'd say. With a prepared query all of the
> >> statements execute the same plan, so you don't need to know the exact
> >> parameters.
> 
> > This isn't true in 8.0 if you are using the unnamed statement (as the
> > JDBC driver does in some cases): the plan chosen depends on the
> > parameter values given in the first Bind.

Oliver,

Yes, I was aware of that, but thought it would confuse the issue.

I agree that it would be ideal if the parameter values from the first
Bind were also logged. However, you don't often need the parameters to
do performance tuning. Initial profiling groups similar statements
together to find the hot spots. We may find other problems like
incorrect SQL, missing join clauses, missing WHERE clauses, need-an-
index etc. Most of which can be done without seeing the exact
parameters. Even if you suspect a wild first bind parameter as the cause
of performance problems, this is still fairly easy to trace - the
question of what do you do about it isn't helped a great deal by knowing
what the value is.

> On Fri, 2005-04-08 at 03:11 -0400, Tom Lane wrote:
> Also, "what plan got chosen" isn't the only question that a DBA might
> want the log to answer.  "Where did my data get screwed up" is at least
> as likely an application.
> 
> I'm a bit worried about the costs of converting binary-format parameters
> into text form ...

Tom

If we have separate requirements, surely they are best met with separate
GUC parameters. For performance analysis purposes we only need to see
the first parameter set, if ever; but we never need to see all of the
parameters.

If we had a log_parameters statements with options:log_parameters = none | first | all
This would give you the capability to log the data as well, if you
required this.

As you point out, there would be performance implications to logging all
of the parameters including both CPU overhead and log volume. There is
also another implication of Data Protection, since you wouldn't
necessarily want to show all people seeing the log your data details.

Anyway, I don't personally see a need or benefit to log parameters in
any case, so I'm happy if anybody wants to raise a TODO item from this,
but its not me.

I've got a patch to submit that logs the EXEC phase, so you get just the
SQL, not the parameters. When we last spoke about this [on ADMIN during
Feb] you mentioned that one of the main reasons that this was not done
before was people couldn't agree exactly how to proceed. In the
meantime, logging just the SQL takes us 95% of the way along the road we
wish to travel.

Best Regards, Simon Riggs