Re: Prepared statements, parameters and logging

Csaba Nagy wrote:

> My real question is: am I turning the right knob with
> "prepareThreshold=0" ? I want all queries to be planned taking into
> account the parameter values by default. I can turn this off on special
> cases by setting the prepare threashold on the statement level - if the
> prepareThreshold is affecting at all the fact that parameters are sent
> for planning or not... is it ?

Short answer: yes, prepareThreshold=0 is the right knob. (with one
exception, see below)

Long answer:

The parameters are always sent out-of-line from the query when using
protocol version 3.

When the driver decides to use a "server prepared statement" (i.e. when
prepareThreshold > 0 and you've reused the same PreparedStatement
enough) it parses the query into a named statement at the protocol
level. Otherwise, it parses it into the unnamed statement.

When a named statement is parsed, the server also generates a generic
plan immediately.

When an unnamed statement is parsed, the parse phase is done but query
planning is not done. Later, when actual parameter values are bound,
planning is done using those actual parameter values for selectivity
purposes.

So the issue is not so much "how are the parameters sent?" but "is a
named statement being used or not?"

If you set prepareThreshold=0 then an unnamed statement should always be
used (& therefore actual parameter values should be taken into account
when planning), with one exception: the setFetchSize() case I described
in my earlier email. In that case the driver simply can't use the
unnamed statement because it needs the statement and portal to survive
for longer than using the unnamed statement would allow. Ideally the
protocol would let clients specify when to defer planning on a
per-statement basis, but the current situation is a result of wanting to
avoid exactly that sort of protocol change.. it's a bit of a compromise.

If you set logLevel=2 on the driver, you should be able to see the
differences in the protocol flow in more detail than the server logging
lets you see.

-O