Thread: closing statements when connection is closed
It would appear from the documentation that we are supposed to clean up JDBC resources when Connecton.close is called, would this include statements. We currently don't track them or clean them up? Opinions ? -- Dave Cramer 519 939 0336 ICQ # 14675561
> It would appear from the documentation that we are supposed to clean up > JDBC resources when Connecton.close is called, would this include > statements. We currently don't track them or clean them up? > > Opinions ? Well, closing a Statement/PreparedStatement should definitely close any open ResultSet objects. Closing a Connection should likely close any open Statements/ResultSets, but that's not been an issue for us since we don't really close our connections (they are returned to a pool instead). David
Dave Cramer wrote: > It would appear from the documentation that we are supposed to clean up > JDBC resources when Connecton.close is called, would this include > statements. We currently don't track them or clean them up? > > Opinions ? We'd need JDK 1.2 to do this properly (weak reference support) otherwise we end up keeping Statements that have not been closed alive as long as the Connection object is. As far as I can see the only additional thing we'd be able to clean up is clearing the reference to row data held by open ResultSet objects. This is only going to have an effect if something outside the driver is holding references to the ResultSet or Statement after closing the connection -- which seems like an application bug to me. -O
I'm no expert on this, but leaving statements open uses memory as I understand it, so if you open new statements instead of re-using them then you should close them. In my case I just wrote a batch pupdate process that runs in about 30 minutes and then exits, so all statements will be closed and memory returned at the time the process ends, but I close them anyway. If your app is intended to run for long periods of time and you are concerned about memory leakage, then you should probably close statements and connections every time you finish with them. Someone wrote that you should clear warnings regulary too, as they can consume memory in long running apps that re-use statements. That makes sense to me. The problem I had with my app was deciding what to do when there was an error. I tried putting calls to the close() method in the catch(SQLException ) block but the compiler complains about it. If anyone has any ideas on that I'd like to hear them. regards Iain ----- Original Message ----- From: "Dave Cramer" <pg@fastcrypt.com> To: <pgsql-jdbc@postgresql.org> Sent: Tuesday, February 10, 2004 1:16 PM Subject: [JDBC] closing statements when connection is closed > It would appear from the documentation that we are supposed to clean up > JDBC resources when Connecton.close is called, would this include > statements. We currently don't track them or clean them up? > > Opinions ? > > -- > Dave Cramer > 519 939 0336 > ICQ # 14675561 > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
(I forgot to CC the list) ----- Original Message ----- From: "Iain" <iain@mst.co.jp> To: <EAdelaRosa@smart.com.ph> Sent: Tuesday, February 10, 2004 2:49 PM Subject: Re: [JDBC] closing statements when connection is closed > > Could you post the exact compile time error for this particular block? > > You may be forgetting to enclose the close() method further > > in a try-catch block :) > > The message was some complaint that the variable might not be initialized. > > It created the situation where I couldn't close the statement if there was > an exception. I think I understand what you mean though, I need to put the > close() statement in a second try block after the one the caused the error? > Or just use a try close block nested in the first catch block. I'll give it > a try. > > I'll dig up the code and the message for you. I'll have to switch my windows > setting back to English though - unless you can read Japanese (I can't but I > still have to work with it - if I switch my windows language setting to > English then my other M$ apps don't work correctly :-( ) > > Thanks > iain >
Hi Eduado,
Thanks for that, I'll be sure to check it out as soon as I can.
I'm using both linux and windows. I wish I could have a windows-less life
but I can't. Windows is ubiquitous.
regards
iain
----- Original Message -----
From: "Eduardo A. dela Rosa" <EAdelaRosa@smart.com.ph>
To: "Iain" <iain@mst.co.jp>
Sent: Tuesday, February 10, 2004 3:33 PM
Subject: Re: [JDBC] closing statements when connection is closed
> Okay, are you using Linux? Please don't switch to Windows! Stay
> with Linux :)
>
> I know now what you're problem is...
>
> You may have declared and initialized your connection object
> within a try/catch block, like this:
>
> private void sqlMethodSQL(){
>
> try{
> Connection con = ds.getConnection();
> ..
> }catch(SQLException sqle){
> try{
> con.close(); // This will result to a compile time error!
> ...
> }catch(SQLException sqle2){
> // never mind
> }
> }
>
> }
>
>
> Rather, try this one:
> ---------------------
>
> private void sqlMethodSQL(){
>
> Connection con = null;
>
> try{
> con = ds.getConnection();
> ..
> }catch(SQLException sqle){
> try{
> con.close();
> ...
> }catch(SQLException sqle2){
> // never mind
> }
> }
>
> }
>
> NOTE: If you are trying to close Statement or ResultSet, too,
> declare and initialize them outside and before the try/catch
> block.
>
> HTH :)
>
> On Tue, 2004-02-10 at 13:51, Iain wrote:
> > (I forgot to CC the list)
> > ----- Original Message -----
> > From: "Iain" <iain@mst.co.jp>
> > To: <EAdelaRosa@smart.com.ph>
> > Sent: Tuesday, February 10, 2004 2:49 PM
> > Subject: Re: [JDBC] closing statements when connection is closed
> >
> >
> > > > Could you post the exact compile time error for this particular
block?
> > > > You may be forgetting to enclose the close() method further
> > > > in a try-catch block :)
> > >
> > > The message was some complaint that the variable might not be
initialized.
> > >
> > > It created the situation where I couldn't close the statement if there
was
> > > an exception. I think I understand what you mean though, I need to put
the
> > > close() statement in a second try block after the one the caused the
> > error?
> > > Or just use a try close block nested in the first catch block. I'll
give
> > it
> > > a try.
> > >
> > > I'll dig up the code and the message for you. I'll have to switch my
> > windows
> > > setting back to English though - unless you can read Japanese (I can't
but
> > I
> > > still have to work with it - if I switch my windows language setting
to
> > > English then my other M$ apps don't work correctly :-( )
> > >
> > > Thanks
> > > iain
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if
your
> > joining column's datatypes do not match
> --
> EDUARDO A. DELA ROSA
> MCOM/3792
>
> http://www.smart.com.ph
>
> contumacious \kahn-too-MAY-shuss\ adjective
>
> o stubbornly disobedient
> o rebellious
I don't see an issue in requiring jdk 1.2, we discussed deprecation of 1.1 support anyway. I didn't envision using the weak references mechanism, but that seems like a good solution. In the JDBC API Tutorial and Reference, it suggests that driver implementors assume the worst, so I think that we should attempt to clean up our clients connections as best we can. Dave On Mon, 2004-02-09 at 23:33, Oliver Jowett wrote: > Dave Cramer wrote: > > It would appear from the documentation that we are supposed to clean up > > JDBC resources when Connecton.close is called, would this include > > statements. We currently don't track them or clean them up? > > > > Opinions ? > > We'd need JDK 1.2 to do this properly (weak reference support) otherwise > we end up keeping Statements that have not been closed alive as long as > the Connection object is. > > As far as I can see the only additional thing we'd be able to clean up > is clearing the reference to row data held by open ResultSet objects. > This is only going to have an effect if something outside the driver is > holding references to the ResultSet or Statement after closing the > connection -- which seems like an application bug to me. > > -O > -- Dave Cramer 519 939 0336 ICQ # 14675561
Dave Cramer wrote: > In the JDBC API Tutorial and Reference, it suggests that driver > implementors assume the worst, so I think that we should attempt to > clean up our clients connections as best we can. Sure. It just seems like a fair amount of work to support a case where we will leak memory regardless of what the driver does. The client is keeping the Statement/ResultSet alive, there's nothing the driver can do to cause GC of that object, at best all we can do is reduce the footprint of the leaked objects. Is doing this worth the extra complexity in the driver? It doesn't look like a common error to me.. > On Mon, 2004-02-09 at 23:33, Oliver Jowett wrote: >>As far as I can see the only additional thing we'd be able to clean up >>is clearing the reference to row data held by open ResultSet objects. >>This is only going to have an effect if something outside the driver is >>holding references to the ResultSet or Statement after closing the >>connection -- which seems like an application bug to me. -O
Oliver, Can you help me out here, I'm considering a pretty simple solution ie WeakHashMap of every statement created, and then iterate over the hashmap at the close and call close on the statement? Is this overly simplistic? Dave On Tue, 2004-02-10 at 08:26, Oliver Jowett wrote: > Dave Cramer wrote: > > In the JDBC API Tutorial and Reference, it suggests that driver > > implementors assume the worst, so I think that we should attempt to > > clean up our clients connections as best we can. > > Sure. It just seems like a fair amount of work to support a case where > we will leak memory regardless of what the driver does. The client is > keeping the Statement/ResultSet alive, there's nothing the driver can do > to cause GC of that object, at best all we can do is reduce the > footprint of the leaked objects. Is doing this worth the extra > complexity in the driver? It doesn't look like a common error to me.. > > > On Mon, 2004-02-09 at 23:33, Oliver Jowett wrote: > >>As far as I can see the only additional thing we'd be able to clean up > >>is clearing the reference to row data held by open ResultSet objects. > >>This is only going to have an effect if something outside the driver is > >>holding references to the ResultSet or Statement after closing the > >>connection -- which seems like an application bug to me. > > -O > -- Dave Cramer 519 939 0336 ICQ # 14675561
Oliver, I agree. ISTM that the suggestion for automatically closing all statements is to free up any server side resource that an open statement might require. Since we in general don't hold any server side resources that exist outside the connection that rational wouldn't apply to our implementation. So the only thing left to be gained is potentially some memory savings, but as you point out we can only reduce the memory not eliminate it since by definition this is only a problem if the application still is referencing the statement objects. --Barry Oliver Jowett wrote: > Dave Cramer wrote: > >> In the JDBC API Tutorial and Reference, it suggests that driver >> implementors assume the worst, so I think that we should attempt to >> clean up our clients connections as best we can. > > > Sure. It just seems like a fair amount of work to support a case where > we will leak memory regardless of what the driver does. The client is > keeping the Statement/ResultSet alive, there's nothing the driver can do > to cause GC of that object, at best all we can do is reduce the > footprint of the leaked objects. Is doing this worth the extra > complexity in the driver? It doesn't look like a common error to me.. > >> On Mon, 2004-02-09 at 23:33, Oliver Jowett wrote: >> >>> As far as I can see the only additional thing we'd be able to clean >>> up is clearing the reference to row data held by open ResultSet >>> objects. This is only going to have an effect if something outside >>> the driver is holding references to the ResultSet or Statement after >>> closing the connection -- which seems like an application bug to me. > > > -O > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
I've never used connection pooling so bear that in mind... connection pooling is best suited for situations where there are many clients that use the DB for short, well defined requests with lots of idle time inbetween (that's what the book I read said anyway). Intuitively, a connection pool is just mimicing a DB to the client anyway, so if you don't explicitly close connections in your own code, how can your connection be returned to the pool (assuming the process doesn't terminate)? No matter what the standard or implementation document says about it's management of connections, I would still require my programmers to explicitly close connections when the application is fnished with them. It's simply good programming practice, and at least that way, if a problem with connection memory leakage occurs you can blame someone else. If I missed the point here, I stand ready to be educated. Regards, Iain > >> In the JDBC API Tutorial and Reference, it suggests that driver > >> implementors assume the worst, so I think that we should attempt to > >> clean up our clients connections as best we can.
Iain, It's really the connections statements I am trying to clean up, but yes you do need to close the connection if that is how the pool expects them returned, or otherwise return them to the pool. If everyone were as diligent as your programmers, writing drivers would be easy, alas, they aren't :( Dave On Wed, 2004-02-11 at 20:30, Iain wrote: > I've never used connection pooling so bear that in mind... connection > pooling is best suited for situations where there are many clients that use > the DB for short, well defined requests with lots of idle time inbetween > (that's what the book I read said anyway). Intuitively, a connection pool is > just mimicing a DB to the client anyway, so if you don't explicitly close > connections in your own code, how can your connection be returned to the > pool (assuming the process doesn't terminate)? > > No matter what the standard or implementation document says about it's > management of connections, I would still require my programmers to > explicitly close connections when the application is fnished with them. It's > simply good programming practice, and at least that way, if a problem with > connection memory leakage occurs you can blame someone else. > > If I missed the point here, I stand ready to be educated. > Regards, > Iain > > > >> In the JDBC API Tutorial and Reference, it suggests that driver > > >> implementors assume the worst, so I think that we should attempt to > > >> clean up our clients connections as best we can. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Dave Cramer 519 939 0336 ICQ # 14675561
Dave Cramer wrote: > Oliver, > > Can you help me out here, I'm considering a pretty simple solution > > ie WeakHashMap of every statement created, and then iterate over the > hashmap at the close and call close on the statement? > > Is this overly simplistic? Sounds like it might work -- I assume you'd have the Statement as the key and null values in the map? -O