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

On Fri, Oct 15, 2010 at 08:56, Radosław Smogura
<rsmogura(at)softperience(dot)eu> wrote:
> On Tue, 12 Oct 2010 20:03:29 +0200, Magnus Hagander <magnus(at)hagander(dot)net>
> wrote:
>> On Tue, Oct 12, 2010 at 17:55, David Fetter <david(at)fetter(dot)org> wrote:
>>> On Tue, Oct 12, 2010 at 10:37:00AM -0500, Kevin Grittner wrote:
>>>> David Fetter <david(at)fetter(dot)org> wrote:
>>>> > Is there something incomplete about the ones I sent, and if so,
>>>> > what?
>>>>
>>>> Well, I'm still curious why it was necessary to modify the server
>>>> side to implement an interface feature for which everything needed
>>>> seems to be present on the client side.
>>>
>>> Not everything is.
>>>
>>> Let's imagine you have a connection pooler with two clients, A and B.
>>> A calls setQueryTimeout, then starts a query, which terminates in
>>> time, but dies before handling it.  B connects to the pool, gets A's
>>> connection, and finds a statement_timeout that's not the default, even
>>> though only A's single query was supposed to have that
>>> statement_timeout.  This is not a situation that can be resolved
>>> without being able to set a timer *on the server side*.
>>
>> Sure it can. The connection pooler just needs to issue a RESET ALL
>> statement when it hands over a connection from one client to another.
>> Isn't that what for example pgbouncer does - at least when configured
>> per instructions?
>>
>> Also, doesn't this affect *all* settings, not just timeout, if it
>> doesn't? Imagine client A executing a SET datestyle for example.
>>
>> AFAICS, any connection pooler that *doesn't* issue a reset between
>> handing this around is broken, isn't it?
>>
>
> If I'm right you would like to say, that when taking connection from pool
> REST ALL should be invoked.

Yes. Unless the client app (or pooler) *knows* that nothing that
executes modifies any settings or session state.

> But... connection pooler will not send RESET ALL in some situations,
> because JDBC driver can have no notify about switching client. In EE
> platforms (e. g. Glassfish), server sometimes creates it's own pool and in
> certain configuration, when you close Connection, server will never pass
> close to PooledConection, nor physical connection. This due to fact, that
> GF and others adds functionality for statements pooling (if server will
> call close on pooled conn, then reusing cached statement will cause error -
> in fact this problem occurs with Hibernate, C3po, XA, and GFv3).

To me, that sounds like a bug in the connection pooler. It is only
safe under quite limited circumstances.

> To summarize, you should never believe that RESET ALL will be called, nor
> any other behavior when switching clients.

In that cas, you ct your client from calling any SET commands etc, and
it should be documented as a major limitation of the connection
pooling software. I don't see any other way to make it safe - others
may have one of course :-)

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/