Re: Connection Pooling, a year later

Bruce Momjian wrote:
> 
> > If implemented, surely the best place to put it would be in libpq?  You
> > could always add a function to lib pq to create a 'pooled' connection,
> > rather than a normal connection.  Basically then the PHP guys would just use
> > that instead of their own pg_connect function.  I guess it would mean that
> > lots of people who use the pgsql client wouldn't have to rewrite their own
> > connection sharing code.
> >
> > However, where would you put all the options for the pool?  Like max
> > processes, min processes, etc.
> >
> > I have learnt that half the problem with connection pooling is transactions
> > that fail to be rolled back...
> 
> The trick for that is to call COMMIT before you pass the backend to a
> new person.  Now, if you want to abort a left-over transaction, you can
> do an ABORT but that is going to show up in the server logs because an
> ABORT without a transaction causes an error message.
> 
> We also have RESET ALL for connection pooling use.

The problem with connection polling, and it can be a very difficult problem, is
the state of the connection. What we saw with The Oracle connection pooling was
if a SQL query took too long, and/or the PHP front end timed out or crashed
(The XML library does this sometimes) that the Oracle connection was in a
strange state. Sometimes the connection stayed active with respect to the
pooling software, but brain dead. The apache processes which was lucky enough
to get that pooled connection either errored or hung.

There were a large number of virtually untraceable problems related to the
previous query and the previous client's behavior.

I know I am being alarmist, but my experience with connection pooling left a
bad taste in my mouth. I can see a persistent connection used per process, but
pooling "n" processes by a "x < n" connections is problematic. The pooling
software has to be able to detect and act upon the real "unexpected" status of
the back-end, not just what it thinks it is.

Most high performance software already has a notion of persistent connection,
which has been debugged and tuned. If there is no real benefit to reducing the
number of back-end processes, I think connection pooling is something that will
more problematic than productive.