Re: setPrepareThreshold

You probably would not want to return a connection like this back to the
pool. The whole point of using a named statement would be to get the
advantage of caching the statement on the server side.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Jan 17, 2014 at 11:55 AM, Robert DiFalco
<robert(dot)difalco(at)gmail(dot)com>wrote:

> I suppose that could be an issue if you weren't using a connection pool
> that periodically closed idle connection or connections that have been use
> X amount of times.
>
> In a way it is counter-intuitive to me. It seems much simpler that there
> would be a setting to have #prepareStatement create the cached statement
> and for close to release it. Then the client processing would match the
> server processing such that if I create a client-side statement cache, the
> statement will represent the server-side cached statement, so that when I
> call a REAL close on the client-side pooled statement it would release
> those resources on the server. This seems more sane to me. But of course I
> don't know all the intricacies of why it was not done this way.
>
>
>
>
> On Fri, Jan 17, 2014 at 7:49 AM, Sehrope Sarkuni <sehrope(at)jackdb(dot)com>wrote:
>
>> On Fri, Jan 17, 2014 at 10:31 AM, Robert DiFalco
>> <robert(dot)difalco(at)gmail(dot)com> wrote:
>> > I was wondering why this setting exists and why it is by default
>> disabled?
>>
>> This setting is the number of times a PreparedStatement needs to be
>> reused before the driver instructs the PostgreSQL server to create a
>> server side prepared statement for it. Server side prepared statements
>> have cached executions plans so in theory they should run a bit
>> faster. The down side is that they take up resources (memory to cache
>> the plan) and since the plan is cached, it won't change if you change
>> based up on the passed in parameters.
>>
>> The default isn't disabled, it's "5". That means that if you execute
>> the same PreparedStatement 5 times it will be converted to server side
>> prepared statement and subsequent executions will use the server side
>> one.
>>
>> > Is there some danger associated with it or some reason I would NOT want
>> it
>> > to be set?
>>
>> If you set it very low and you have a lot of different SQL statements
>> that aren't actually being reused, then you will have a lot of
>> prepared statements on the server side taking up needless resources.
>> The default of "5" should be fine for most purposes.
>>
>> Check out these links for more details:
>>
>> http://jdbc.postgresql.org/documentation/81/server-prepare.html
>>
>> http://stackoverflow.com/questions/8104595/why-is-the-postgresql-jdbc-prepared-statement-threshold-defaulted-to-5
>>
>> Regards,
>> -- Sehrope Sarkuni
>>
>
>