On 15/10/2010 10:38 PM, Tom Lane wrote:
Yeah, actually what you need is DISCARD ALL when reassigning a connection to another client. Anything less than that assumes the clients are cooperating closely, ie they *want* the same prepared statements etc.
For what it's worth, this is quite common in the world of web apps. Java EE application servers, in particular, tend to offer per-application connection pools that can significantly benefit from this sort of thing.
I don't know how much that sort of co-operating group of apps is likely to use external pooling via PgPool and friends, though. Most such apps have an *internal* connection pool, whether managed by an appserver, web server, or by the app code its self.
The JDBC driver's org.postgresql.ds.PGPoolingDataSource is rather unclear about how it behaves in terms of resetting GUCs, resetting roles, clearing prepared statements etc between connection uses, so it's not clear what category it falls into. The docs suggest it's a bit of a toy implementation that's not intended for real-world production use, though. OTOH, it's not clear how connection pools like DBCP should know how or when to do this when returning a PostgreSQL connection to the pool, so it may well be an issue for "serious" non-Pg-specific pools too. The JDBC spec doesn't seem to offer a generic "reset this connection to defaults" method for use when pooling a connection.
But even if you make that assumption, a pooler that isn't even capable of sending an ABORT between clients doesn't seem usable to me. For example, if a client loses its network connection mid-transaction, that failure will cascade to other clients if you don't have any ability to reset the database session before handing it to another client.
You can never really assume that the connection you get from a pool (or have directly made) is working and usable, though. You always have to be prepared to handle failures because someone trips over an Ethernet cable, etc, so you can get a fresh connection and re-issue your transaction.
Nonetheless, I tend to agree that pools should make some effort to handle failures in one connection that indicate likely failure in all other connections, re-testing all the connections before handing them out to clients.
-- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
