Re: Prepared Statements vs. pgbouncer

On Mon, Oct 01, 2007 at 06:52:50PM +0200, Till Toenges wrote:
> Paul Lindner wrote:
> >1) Use hashing to choose a prepared statement name
> >
> >   If we take the hash of the prepared statement text and prefix with
> >   S_ we can be assured of using the same unique prepared statement
> >   name across all application servers.
> >
> >   (And yes, I know that hashes are not perfect and collisions can
> >    occur.  Highly unlikely if we choose a good hash)
>
> This sounds like a bad idea on many levels. Don't do it. Actually, i
> think the driver is ok and the real problem is pgbouncer.

Actually the problem is that Postgres is process/session oriented in a
multithreaded event-based world.

> I have another solution to your problem:
>
> You said that there are 100s of Servers, i assume that there is some
> money behind that. Talk to the pgbouncer developers. Ask them to analyze
> your problem and what it would cost to fix it. Ask them what it would
> cost to make pgbouncer fully compliant, because it's probably not the
> only feature you will miss. Figure out if this is cheaper than replacing
> / upgrading your hardware with something powerful enough to handle the
> load without pgbouncer. Pick the cheaper solution. Done.

The problem is that the described solution puts way too much moving
parts inside of something that should be very simple.  You'd have to
recreate most of Postgresql's parsing and grammar inside of Pgbouncer
and change it from something simple into a full-featured proxy.

In my opinion the _correct_ way to fix the problem of too many bloated
idle backends is to incorporate libevent into Postgres and radically
rethink of ways to maintain cache/session coherence.

> "Fixing" the jdbc driver is probably not a stable long term solution,
> even if it helps with your most immediate problem.

Fixing the JDBC driver is the simplest and cheapest alternative.  So I
am following your advice and picking the cheaper solution :)

--
Paul Lindner        ||||| | | | |  |  |  |   |   |
lindner@inuus.com