Re: Connection Pooling, a year later

As long as each client's call is composed of a standalone transaction, there 
is no problem with external connection pools. But what about when a client's 
transactions spans two or more calls, such as SELECT FOR UPDATE? Then pooling 
is not safe: it offers no assurance of what may be interjected into an open 
transaction between calls. For example, each is a separate call to a shared 
connection:

Client A:  BEGIN WORK; SELECT last_name from customer for update where <X>;

Client B:  BEGIN WORK; SELECT street from customer for update where <Y>;

Client A:  update customer set lastname=<modified value> where <X>; COMMIT 
WORK;


Now, isn't Client B's write lock gone with Client A's commit? Yet Client A's 
lock is still hanging around. While Client B's commit will close it, Client B 
has lost the assurance of its lock, defeating the purpose of SELECT FOR 
UPDATE.

If this is corrent, then external connection pools limit what you can do with 
the database to a single call. Any transaction spanning more than one call is 
unsafe, because it is not isolated from other clients sharing the same 
connection.



On Tuesday 18 December 2001 11:30 pm, Andrew McMillan wrote:
> On Tue, 2001-12-18 at 13:46, Michael Owens wrote:
> > By having the postmaster map multiple clients to a fixed number of
> > backends, you achieve the happy medium: You never exceed the ideal number
> > of active backends, and at the same time you are not limited to only
> > accepting a fixed number of connections. Accepting connections can now be
> > based on load (however you wish to define it), not number.  You now make
> > decisions based on utlization.
> >
> > If it were shown that even half of a backend's life consisted of idle
> > time, leasing out that idle time to another active connection would
> > potentially double the average number of simultaneous requests without
> > (theoretically) incurring any significant degradation in performance.
>
> Have you looked at the client-side connection pooling solutions out
> there?
>
> DBBalancer ( http://dbbalancer.sourceforge.net/ ) tries to sit very
> transparently between your application and PostgreSQL, letting you
> implement connection pooling with almost no application changes.
>
> There was another one I came across too, but that one requires you to
> make more wide-reaching changes to the application.
>
> In my applications I have found DBBalancer to be roughly the same level
> of performance as PHP persistent connections, but a lot fewer
> connections are needed in the pool because they are only needed when
> Apache is delivering dynamic content - not the associated static
> stylesheets and images.
>
> Regards,
>                     Andrew.