Re: PreparedStatement cache and dynamic queries

On 1-Mar-08, at 11:00 AM, Ken Johanson wrote:

> I'm trying to assess how a large in-house app will behave with PG.
> The obvious and most correct way to handle the situation is to re-
> code their apps to /not/ send dynamic queries to
> Connection.prepareStatement() at all :-) I'm curious though, about
> how PG and/or the JDBC driver generally handles prepared statements
> (now, and future/ideally):
>
> 1) Are statements cached server side or driver?
>
There is some caching of a statement on the server per connection.

> 2) If client side, are statements cached per-connection, or pooled
> in a classloader (or even jvm classloader)?

You can use a connection pool like dbcp to cache statements on the
client side.
>
>
>     (their connection pool impl allows creation of prepared statements
> bound to pooled Connections -- caching only at the connection level
> * 50 connections with prepareStatement(dynamicSql) has implications
> since the list can be several thousand items long and in random order)
>
> 3) Can we set a maximum time-to-live so that only frequently reused
> statements stay cached, so that if there is dynamic sql sent to
> Con.prepareStatement, will not be a memory leak (see next)?

> 4) They want parameter escaping but in the context of lists:
>     WHERE foo IN ('a','b','c',...dynamic list).
> Is this possible in a database neutral way?
>
> String[] ar = new String[]{"a","b"};
> "WHERE foo IN ?",
> ps.setObject(1,ar,Types.ARRAY);
>
> (I've never tried this snippet but presume it won't work due to the
> zero-len case which should failfast according to sql, I believe)
>
As far as I know this won't work
> Thanks,
> Ken
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster