Thread: PreparedStatement cache and dynamic queries

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?

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

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

Thanks,
Ken

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

Dave Cramer <pg@fastcrypt.com> writes:
> On 1-Mar-08, at 11:00 AM, Ken Johanson wrote:
>> 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

Since about 8.2 the backend has been able to cater to this type of thing
using "scalar = ANY(array)" syntax: that does support pushing the whole
array through as one parameter and it doesn't fail for the zero-elements
case.  It won't work with IN though; AFAICT the code snippet shown above
is a flat violation of the SQL spec.  (The ANY(array) business isn't in
the SQL spec either, but since the spec doesn't assign a meaning to ANY
except with a subquery as argument, we felt we could get away with this
extension.)

Since this behavior is nowhere to be found in the spec, imagining that
you can have it in a "database neutral way" is sheer fantasy.  Any DB
that can do it at all will have made their own choices about how to
shoehorn it into SQL syntax.

            regards, tom lane