PreparedStatement cache and dynamic queries

From: Ken Johanson <pg-user(at)kensystem(dot)com>
To: pgsql-jdbc(at)postgreSQL(dot)org
Subject: PreparedStatement cache and dynamic queries
Date: 2008-03-01 16:00:32
Message-ID: 47C97DA0.2090908@kensystem.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2008-03-01 17:49:28 Re: PreparedStatement cache and dynamic queries
Previous Message dmp 2008-03-01 04:47:18 Re: Summer of Code Java?