Skip site navigation (1) Skip section navigation (2)

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

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: Radosław Smogura <rsmogura(at)softperience(dot)eu>
Cc: David Fetter <david(at)fetter(dot)org>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, PG Hackers <pgsql-hackers(at)postgresql(dot)org>, PostgreSQL JDBC List <pgsql-jdbc(at)postgresql(dot)org>, robertmhaas(at)gmail(dot)com, tgl(at)sss(dot)pgh(dot)pa(dot)us
Subject: Re: [JDBC] Support for JDBC setQueryTimeout, et al.
Date: 2010-10-15 08:37:05
Message-ID: AANLkTinFMPHaa1SH-uf7fqBTkVcMZYRpxqjaaYUKHEHA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
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/

In response to

Responses

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2010-10-15 08:57:41
Subject: Re: Extensions, this time with a patch
Previous:From: Devrim GÜNDÜZDate: 2010-10-15 08:36:26
Subject: Re: Slow count(*) again...

pgsql-jdbc by date

Next:From: Radosław SmoguraDate: 2010-10-15 09:11:44
Subject: Re: [JDBC] Support for JDBC setQueryTimeout, et al.
Previous:From: Radosław SmoguraDate: 2010-10-15 06:56:21
Subject: Re: [JDBC] Support for JDBC setQueryTimeout, et al.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group