Re: PGStatement#setPrepareThreshold

Oliver,

Thanks for your answer.

> The logic looks like:
> - On statement creation set count=0
> - On each execution:
> - If this statement is a PreparedStatement, increment count
> - If threshold == 0 or count < threshold, make this execution "oneshot"
> - Execute query

OK, I've checked the sources. I'm using postgresql-jdbc-8.1dev-400.
The relevant piece of code looks to me to be in
AbstractJdbc2Statement#execute(Query,ParameterList,int):

// Only use named statements after we hit the threshold
if (preparedQuery != null)
{
++m_useCount; // We used this statement once more.
if (m_prepareThreshold == 0 || m_useCount <
m_prepareThreshold)
flags |= QueryExecutor.QUERY_ONESHOT;
}

So if preparedQuery is not null for prepared statements, it should work
as you said...

> "oneshot" queries use the unnamed statement (with one exception: queries
> that will be backed by a portal use a named statement)
>
> > I couldn't figure out this from the log files... postgres logs the
> > queries as <unnamed>, but that doesn't tell me too much.
>
> If you're seeing <unnamed> then those queries aren't using server-side
> prepare (the unnamed statement is also special as it's the trigger for
> the planner behaviour that you are trying to avoid..) .. so it seems
> that you are not managing to trigger server-side prepare for some
> reason. Maybe you are using a plain Statement?

No, I definitely use a prepared statement, I have lots of parameters in
the IN clause... that's part of the problem. And I checked again, and it
is logged as <unnamed> in the postgres logs.

So the only remaining suspect is that the threshold is not really set to
1. This is somewhat strange, as I use a connection pool and set it to 1
on each connection, and only set it to 0 on specific statements where I
do want the parameter values to be taken into account (I know, I'll have
to change this, but it was the easiest way to get the system stable
after switching from Oracle to postgres).

I will have to investigate what is the real problem.

Thanks,
Csaba.