[odb-users] Problem with using ODB with database connection pooler

Boris Kolpackov boris at codesynthesis.com
Fri Jan 17 06:12:21 EST 2014


Hi Aleksey,

Енакаев Алексей <Aleksey.Enakaev at infotecs.ru> writes:

> The matter is we use database connection pooler (pgbouncer) in
> "transaction" mode. It turned out in this kind of mode pgbouncer
> does not support prepared statements, but ODB manual says "all the
> non-query database operations such as persist(), load(), update(),
> etc., are implemented in terms of prepared statements that are
> cached and reused". The question is if there is some way to prevent
> using prepared statement with non-query database operations?

There is no way to disable prepared statements in ODB (and it would
probably be a bad idea from the performance point of view). What
you could do, as a workaround, is to make ODB release the connection
(and therefore prepared statements) after each transaction. This way
it will "match" what pgbouncer is doing. You may end up with pretty
poor performance, though.

If you want to try this, you can use the new_connection_factory
instead of the default connection_pool_factory. See Section 19.3,
"PostgreSQL Connection and Connection Factory" for details.

On a more general note, you didn't say what you are trying to achieve
by using pgbouncer. E.g., is it just a connection pooler to a single
server? Or is it used as a load balancer between multiple servers?

If it is just a connection pooler (i.e., it is used to reduce the
overhead of establishing new connections to PG), then it is not
really necessary since the default ODB connection_pool_factory
already caches the connections. Even if this is not exactly what
you want, you may be able to achieve better results by implementing
your own connection_factory (e.g., release connections after some
time of inactivity, or some such). I think not being able to use
prepared statements is too drastic.

Boris



More information about the odb-users mailing list