Re: [JDBC] Support for JDBC setQueryTimeout, et al.

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/