Re: setPrepareThreshold

Not sure I understand? I would want to pool a prepared statement like this.
Then when I close on the real PreparedStatement or Connection, all the
server side cached resources would be released. What am I missing?

On Fri, Jan 17, 2014 at 9:01 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> 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
>>>
>>
>>
>