Re: PGStatement#setPrepareThreshold - Mailing list pgsql-jdbc

Ok, further investigation shows that it does work

However I'm still puzzled by these backend logs

<test test>LOG:  statement: PREPARE S_3 AS INSERT INTO texttable (te)
VALUES ($1)
<test test>LOG:  statement: <BIND>
<test test>LOG:  statement: EXECUTE <unnamed>  [PREPARE:  INSERT INTO
texttable (te) VALUES ($1)]

We see the prepare to a named statement, but then the execute is
unnamed ?

Dave

On 3-Aug-06, at 7:53 AM, Dave Cramer wrote:

> Csaba,
>
> Actually I was debugging some other code and noticed that it
> doesn't really get set, this just confirms my suspicions. I'll try
> to get something out in a few hours
>
> Dave
> On 3-Aug-06, at 5:17 AM, Csaba Nagy wrote:
>
>> 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.
>>
>>
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>