Re: Connection pooling. - Mailing list pgsql-hackers

* Tom Lane <tgl@sss.pgh.pa.us> [000711 22:53] wrote:
> Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes:
> > Seems a lot trickier than you think. A backend can only be running
> > one transaction at a time, so you'd have to keep track of which backends
> > are in the middle of a transaction. I can imagine race conditions here.
> 
> Aborting out of a transaction is no problem; we have code for that
> anyway.  More serious problems:
> 
> * We have no code for reassigning a backend to a different database,
>   so the pooling would have to be per-database.

That would need to be fixed.  How difficult would that be?

> * AFAIK there is no portable way to pass a socket connection from the
>   postmaster to an already-existing backend process.  If you do a
>   fork() then the connection is inherited ... otherwise you've got a
>   problem.  (You could work around this if the postmaster relays
>   every single byte in both directions between client and backend,
>   but the performance problems with that should be obvious.)

no, see below.

> > And backends can have contexts that are set by various clients using
> > SET and friends.
> 
> Resetting SET variables would be a problem, and there's also the
> assigned user name to be reset.  This doesn't seem impossible, but
> it does seem tedious and error-prone.  (OTOH, Peter E's recent work
> on guc.c might have unified option-handling enough to bring it
> within reason.)

What can be done is that each incomming connection can be assigned an
ID into a system table.  As options are added the system would assign
them to key-value pairs in this table.  Once someone detects that the
remote side has closed the connection the data can be destroyed, but
until then along with the descriptor passing the ID of the client
as an index into the table can be passed for the backend to fetch.

> The killer problem here is that you can't hand off a connection
> accepted by the postmaster to a backend except by fork() --- at least
> not with methods that work on a wide variety of Unixen.  Unless someone
> has a way around that, I think the idea is dead in the water; the lesser
> issues don't matter.

The code has been around since 4.2BSD, it takes a bit of #ifdef to
get it right on all systems but it's not impossible, have a look at
http://www.fhttpd.org/ for a web server that does this in a portable
fashion.

I should have a library whipped up for you guys really soon now
to handle the descriptor and message passing.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."