Skip site navigation (1) Skip section navigation (2)

Re: PreparedStatement cache and dynamic queries

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Ken Johanson <pg-user(at)kensystem(dot)com>
Cc: pgsql-jdbc(at)postgreSQL(dot)org
Subject: Re: PreparedStatement cache and dynamic queries
Date: 2008-03-01 17:49:28
Message-ID: B3F21BFD-DAE3-46BB-B75F-3641C18DBDA4@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

Responses

pgsql-jdbc by date

Next:From: Tom LaneDate: 2008-03-01 18:33:56
Subject: Re: PreparedStatement cache and dynamic queries
Previous:From: Ken JohansonDate: 2008-03-01 16:00:32
Subject: PreparedStatement cache and dynamic queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group