Message-ID: <4C4A86C5.7070301@postnewspapers.com.au>
Date: Sat, 24 Jul 2010 14:23:01 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100423 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Pooling in Core WAS: Need help in performance tuning.
References: <AANLkTiknhZhKtGDidBsMWtPJpRgln9DrrsCebCEYk5XE@mail.gmail.com>
	<8760.1278650566@sss.pgh.pa.us>	<1278680186.1914.36.camel@bnicholson-desktop>
	<4C36F57102000025000332F6@gw.wicourts.gov>
	<alpine.DEB.2.00.1007091621380.2534@aragorn.flymine.org>
	<4C36FEAD02000025000332FC@gw.wicourts.gov>
	<alpine.DEB.2.00.1007091753350.2534@aragorn.flymine.org>
	<4C37A9C1.1060405@2ndquadrant.com>	<4C37791C0200002500033379@gw.wicourts.gov>
	<4C37CBFB.1000800@agliodbs.com>	<4C37EA1D.6000008@postnewspapers.com.au>
	<AANLkTimNJZlX7kFH_r0-BB_K3NGorU7BHd85SxXJbuGJ@mail.gmail.com>
	<4C38BF03.6060503@agliodbs.com>	<19244.1278787866@sss.pgh.pa.us>
	<alpine.DEB.2.00.1007121044330.2534@aragorn.flymine.org>
	<4C3AF564.5030204@postnewspapers.com.au>
	<AANLkTinDgkzqf_=O+hk5OMq+AxN4G+fpfQZM4DBizAOF@mail.gmail.com>
	<1279826144.14271.5.camel@hvost>
	<AANLkTimLr9AoMMB_k9GWuEgoBZg1YWxR5OqKY-1Ugimc@mail.gmail.com>
	<1279900707.26952.4.camel@hvost>
	<AANLkTi=i+b2LbUj0QrvD_zzDW15KVmLpijawwS22KKYu@mail.gmail.com>
In-Reply-To: <AANLkTi=i+b2LbUj0QrvD_zzDW15KVmLpijawwS22KKYu@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 24/07/10 01:28, Robert Haas wrote:

> Well, if we could change the backends so that they could fully
> reinitialize themselves (disconnect from a database to which they are
> bound, etc.), I don't see why we couldn't use the Apache approach.

This would offer the bonus on the side that it'd be more practical to
implement database changes for a connection, akin to MySQL's "USE".
Inefficient, sure, but possible.

I don't care about that current limitation very much. I think anyone
changing databases all the time probably has the wrong design and should
be using schema. I'm sure there are times it'd be good to be able to
switch databases on one connection, though.


My question with all this remains: is it worth the effort when external
poolers already solve the problem. Can PostgreSQL offer tools and
interfaces to permit external poolers to solve the problems they have,
rather than trying to bring them in-core? For example, with auth, can
the Pg server offer features to help poolers implement passthrough
authentication against the real Pg server?

Perhaps Pg could expose auth features over SQL, permitting appropriately
privileged users to verify credentials with SQL-level calls. Poolers
could pass supplied user credentials through to the real Pg server for
verification. For bonus points, an SQL interface could be provided that
lets the super-priveleged auth managing connection be used to change the
login role of another running backend/connection, so the pooler could
hand out connections with different login user ids without having to
maintain a pool per user id.

( That'd probably also permit implementation of a "CHANGE USER" command,
where the client changed login roles on the fly by passing the
credentials of the new role. That'd be *awesome* for application server
connection pools. )

--
Craig Ringer