Re: setPrepareThreshold

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
>