Re: Prepared Statements vs. pgbouncer

Oliver Jowett wrote:
> Josh Berkus wrote:
>> Oliver,
>>
>>> S_1 from client 1 might be a completely different query to S_1 from
>>> client 2. The JDBC driver just numbers statements sequentially as
>>> they are used.
>>
>> So, how does j2EE-side connection pooling handle this?
>
> Err.. it is dealing with JDBC connections, not protocol level stuff, so
> there's no issue with statement naming. The driver just does what it
> normally does (maintains a mapping of query to statement name on each
> connection). The usual J2EE connection pool model is different to what
> pgbouncer apparently does as clients explicitly return connections to
> the pool when no longer needed -- which effectively invalidates any
> PreparedStatement objects they might still be holding -- and reobtain
> them when later need.

The OP might be better off doing the connection multiplexing at a higher
level as well. You could use something like Virtual JDBC
(vjdbc.sourceforge.net) between the clients and a connection pool
managed in the multiplexer. There would be no problem with prepared
statements, since the JDBC driver would deal with real connections to
the database. I've never used vjdbc myself, so I don't know if it's
applicable, but something along those lines should work.

You might also consider if using pgbouncer or similar really is
necessary. Perhaps you could get away with a per-client connection pool,
with "min pool size" small enough, maybe 0, that the number of
simultaneous connections to the server would stay reasonable.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com