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/