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

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.

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 summarize, you should never believe that RESET ALL will be called, nor
any other behavior when switching clients.

Am I right?
----------
Radosław Smogura
http://www.softperience.eu