Thread: Why does Statement.close() close result set?
I've been trying to get erserver to work with the current JDBC driver,
and finding that it doesn't work very well. The problem is that there
are many places in which a function creates a Statement, executes it
to get a ResultSet, closes the Statement, and returns the ResultSet to
its caller. This pattern worked okay in JDBC 7.0, but it fails
completely with the current driver, because Statement.close() thinks
it should close the last result set returned by the statement.
I've been able to sort-of work around this by commenting out the
explicit close calls, but this doesn't really work, because the
created Statement object has no references once control has left
the calling function. A garbage-collection pass would finalize the
Statement and thereby zap the ResultSet, whether or not there are
still any valid references to the ResultSet.
The above programming pattern seems perfectly valid to me, and
accordingly I think that Statement.close() is broken. The current
behavior foregoes all the advantages of Java's memory management model
and turns them into liabilities. Instead of letting garbage collection
do what it's supposed to, the programmer is forced to hang onto
references to one object in order to preserve the validity of a
different object.
Comments?
regards, tom lane
BTW, the error reported by ResultSet.next() in this situation is
extremely misleading; it gripes about "connection closed" when it
should say "result set closed".
Tom, A ResultSet object is closed when it's Statement object is closed per the JDBC spec. Look at the Javadoc on Statement.close() and it states "When a Statement object is closed, its current ResultSet object, if one exists, is also closed." At a higher level it also says that close "Releases this Statement object's database and JDBC resources immediately ..." and the ResultSet is one of those resources. Kevin Tom Lane wrote: >I've been trying to get erserver to work with the current JDBC driver, >and finding that it doesn't work very well. The problem is that there >are many places in which a function creates a Statement, executes it >to get a ResultSet, closes the Statement, and returns the ResultSet to >its caller. This pattern worked okay in JDBC 7.0, but it fails >completely with the current driver, because Statement.close() thinks >it should close the last result set returned by the statement. > >I've been able to sort-of work around this by commenting out the >explicit close calls, but this doesn't really work, because the >created Statement object has no references once control has left >the calling function. A garbage-collection pass would finalize the >Statement and thereby zap the ResultSet, whether or not there are >still any valid references to the ResultSet. > >The above programming pattern seems perfectly valid to me, and >accordingly I think that Statement.close() is broken. The current >behavior foregoes all the advantages of Java's memory management model >and turns them into liabilities. Instead of letting garbage collection >do what it's supposed to, the programmer is forced to hang onto >references to one object in order to preserve the validity of a >different object. > >Comments? > > regards, tom lane > >BTW, the error reported by ResultSet.next() in this situation is >extremely misleading; it gripes about "connection closed" when it >should say "result set closed". > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > >
And about ResultSet the API documentation says: "A |ResultSet| object is automatically closed when the |Statement| object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results." This implies that there is (should be) no valid ResultSets for a Statement other than the "current" ResultSet. This completes the passage quoted below. Peter Kevin Schmidt wrote: > Tom, > > A ResultSet object is closed when it's Statement object is closed per > the JDBC spec. Look at the Javadoc on Statement.close() and it states > "When a Statement object is closed, its current ResultSet object, if > one exists, is also closed." At a higher level it also says that > close "Releases this Statement object's database and JDBC resources > immediately ..." and the ResultSet is one of those resources. > > Kevin > > Tom Lane wrote: > >> I've been trying to get erserver to work with the current JDBC driver, >> and finding that it doesn't work very well. The problem is that there >> are many places in which a function creates a Statement, executes it >> to get a ResultSet, closes the Statement, and returns the ResultSet to >> its caller. This pattern worked okay in JDBC 7.0, but it fails >> completely with the current driver, because Statement.close() thinks >> it should close the last result set returned by the statement. >> >> I've been able to sort-of work around this by commenting out the >> explicit close calls, but this doesn't really work, because the >> created Statement object has no references once control has left >> the calling function. A garbage-collection pass would finalize the >> Statement and thereby zap the ResultSet, whether or not there are >> still any valid references to the ResultSet. >> >> The above programming pattern seems perfectly valid to me, and >> accordingly I think that Statement.close() is broken. The current >> behavior foregoes all the advantages of Java's memory management model >> and turns them into liabilities. Instead of letting garbage collection >> do what it's supposed to, the programmer is forced to hang onto >> references to one object in order to preserve the validity of a >> different object. >> >> Comments? >> >> regards, tom lane >> >> BTW, the error reported by ResultSet.next() in this situation is >> extremely misleading; it gripes about "connection closed" when it >> should say "result set closed". >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 8: explain analyze is your friend >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Tom, this is what the spec says for Statement.close() Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources. Note: A Statement object is automatically closed when it is garbage collected. When a Statement object is closed, its current ResultSet object, if one exists, is also closed Dave On Fri, 2003-10-10 at 11:21, Tom Lane wrote: > I've been trying to get erserver to work with the current JDBC driver, > and finding that it doesn't work very well. The problem is that there > are many places in which a function creates a Statement, executes it > to get a ResultSet, closes the Statement, and returns the ResultSet to > its caller. This pattern worked okay in JDBC 7.0, but it fails > completely with the current driver, because Statement.close() thinks > it should close the last result set returned by the statement. > > I've been able to sort-of work around this by commenting out the > explicit close calls, but this doesn't really work, because the > created Statement object has no references once control has left > the calling function. A garbage-collection pass would finalize the > Statement and thereby zap the ResultSet, whether or not there are > still any valid references to the ResultSet. > > The above programming pattern seems perfectly valid to me, and > accordingly I think that Statement.close() is broken. The current > behavior foregoes all the advantages of Java's memory management model > and turns them into liabilities. Instead of letting garbage collection > do what it's supposed to, the programmer is forced to hang onto > references to one object in order to preserve the validity of a > different object. > > Comments? > > regards, tom lane > > BTW, the error reported by ResultSet.next() in this situation is > extremely misleading; it gripes about "connection closed" when it > should say "result set closed". > _______________________________________________ > Erserver-general mailing list > Erserver-general@gborg.postgresql.org > http://gborg.postgresql.org/mailman/listinfo/erserver-general > > -- Dave Cramer <Dave@micro-automation.net>
On Fri, Oct 10, 2003 at 11:21:45AM -0400, Tom Lane wrote: > I've been trying to get erserver to work with the current JDBC driver, > and finding that it doesn't work very well. The problem is that there > are many places in which a function creates a Statement, executes it > to get a ResultSet, closes the Statement, and returns the ResultSet to > its caller. This pattern worked okay in JDBC 7.0, but it fails > completely with the current driver, because Statement.close() thinks > it should close the last result set returned by the statement. As others have said, this behaviour is required by the JDBC spec. This actually makes sense in the general case, as a ResultSet implementation that doesn't read all data at once may rely on resources managed at the Statement level for continued operation. > I've been able to sort-of work around this by commenting out the > explicit close calls, but this doesn't really work, because the > created Statement object has no references once control has left > the calling function. A garbage-collection pass would finalize the > Statement and thereby zap the ResultSet, whether or not there are > still any valid references to the ResultSet. Actually this should work fine as a ResultSet must hold a reference (directly or indirectly) to the Statement that created it to implement ResultSet.getStatement(). The postgresql driver's ResultSet implementation holds this reference directly in a field of AbstractJdbc1ResultSet. So whatever GC-related problems you are seeing are likely to have another cause. Relying on finalization to do resource cleanup can be risky, though, since it's not guaranteed to happen in a timely fashion (or ever, for that matter). Witness the fun JDK1.4 has with NIO direct buffers & GC .. -O
On 13/10/2003 01:23 Oliver Jowett wrote: > [snip] > As others have said, this behaviour is required by the JDBC spec. This > actually makes sense in the general case, as a ResultSet implementation > that > doesn't read all data at once may rely on resources managed at the > Statement > level for continued operation. > > > I've been able to sort-of work around this by commenting out the > > explicit close calls, but this doesn't really work, because the > > created Statement object has no references once control has left > > the calling function. A garbage-collection pass would finalize the > > Statement and thereby zap the ResultSet, whether or not there are > > still any valid references to the ResultSet. > > Actually this should work fine as a ResultSet must hold a reference > (directly or indirectly) to the Statement that created it to implement > ResultSet.getStatement(). The postgresql driver's ResultSet > implementation > holds this reference directly in a field of AbstractJdbc1ResultSet. So > whatever > GC-related problems you are seeing are likely to have another cause. > > Relying on finalization to do resource cleanup can be risky, though, > since > it's not guaranteed to happen in a timely fashion (or ever, for that > matter). Witness the fun JDK1.4 has with NIO direct buffers & GC .. ISTM that calling resultset.getStatement().close() after processing the resultset might provide a reasonable fix. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Tom, this is what the spec says for Statement.close() Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources. Note: A Statement object is automatically closed when it is garbage collected. When a Statement object is closed, its current ResultSet object, if one exists, is also closed Dave On Fri, 2003-10-10 at 11:21, Tom Lane wrote: > I've been trying to get erserver to work with the current JDBC driver, > and finding that it doesn't work very well. The problem is that there > are many places in which a function creates a Statement, executes it > to get a ResultSet, closes the Statement, and returns the ResultSet to > its caller. This pattern worked okay in JDBC 7.0, but it fails > completely with the current driver, because Statement.close() thinks > it should close the last result set returned by the statement. > > I've been able to sort-of work around this by commenting out the > explicit close calls, but this doesn't really work, because the > created Statement object has no references once control has left > the calling function. A garbage-collection pass would finalize the > Statement and thereby zap the ResultSet, whether or not there are > still any valid references to the ResultSet. > > The above programming pattern seems perfectly valid to me, and > accordingly I think that Statement.close() is broken. The current > behavior foregoes all the advantages of Java's memory management model > and turns them into liabilities. Instead of letting garbage collection > do what it's supposed to, the programmer is forced to hang onto > references to one object in order to preserve the validity of a > different object. > > Comments? > > regards, tom lane > > BTW, the error reported by ResultSet.next() in this situation is > extremely misleading; it gripes about "connection closed" when it > should say "result set closed". > _______________________________________________ > Erserver-general mailing list > Erserver-general@gborg.postgresql.org > http://gborg.postgresql.org/mailman/listinfo/erserver-general > >
Tom Lane wrote: > I've been trying to get erserver to work with the current JDBC driver, > and finding that it doesn't work very well. The problem is that there > are many places in which a function creates a Statement, executes it > to get a ResultSet, closes the Statement, and returns the ResultSet to > its caller. This pattern worked okay in JDBC 7.0, but it fails > completely with the current driver, because Statement.close() thinks > it should close the last result set returned by the statement. > > I've been able to sort-of work around this by commenting out the > explicit close calls, but this doesn't really work, because the > created Statement object has no references once control has left > the calling function. A garbage-collection pass would finalize the > Statement and thereby zap the ResultSet, whether or not there are > still any valid references to the ResultSet. > > The above programming pattern seems perfectly valid to me, and > accordingly I think that Statement.close() is broken. The current > behavior foregoes all the advantages of Java's memory management model > and turns them into liabilities. Instead of letting garbage collection > do what it's supposed to, the programmer is forced to hang onto > references to one object in order to preserve the validity of a > different object. > > Comments? How is the automatic cursor code controlled by setFetchSize() affected by a Statement.close() before the result set is consumed? Does that need to use holdable cursors here because in turn the transaction around the cursor could end sooner than expected? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > How is the automatic cursor code controlled by setFetchSize() affected > by a Statement.close() before the result set is consumed? Does that need > to use holdable cursors here because in turn the transaction around the > cursor could end sooner than expected? > > > Jan > This is an interesting question. The current logic for cursor backed result sets will error if you try to access the result set after a commit/rollback (actually you will get an error when you exhaust the already fetched values that are in memory and try to fetch more from the database). This behavior is allowed by the jdbc spec. In fact there are methods in Connection, DatabaseMetaData and Statement dealing with "Result Set Holdability" as of jdbc3/jdk1.4. Most of these methods are currently not implemented by the jdbc driver however. And further more the nature of the methods are such that they envision the 'holdability' setting to be one set at the Connection level, as opposed to something that might vary from ResultSet to ResultSet as it would need to in our current implementation. We could support ResultSets being usable across transaction boundaries for both the regular and cursor backed cases if WITH HOLD was used for 7.4 servers. I would be inclined to think that we would do this only if the client insisted on requiring cursors be held over transaction boundaries. Given what I remember of the implementation discussions on WITH HOLD, I wouldn't want that to be the default (or am I remembering it wrong and WITH HOLD doesn't have any adverse side effects that might be nasty). thanks, --Barry
(cc: list trimmed) On Tue, Oct 14, 2003 at 06:08:02PM -0700, Barry Lind wrote: > Most of these methods are currently not implemented by the jdbc driver > however. And further more the nature of the methods are such that they > envision the 'holdability' setting to be one set at the Connection > level, as opposed to something that might vary from ResultSet to > ResultSet as it would need to in our current implementation. Actually you can specify holdability per statement, much like resultset type and concurrency -- see: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#createStatement(int,%20int,%20int) -O
Barry Lind wrote: > Jan Wieck wrote: >> How is the automatic cursor code controlled by setFetchSize() affected >> by a Statement.close() before the result set is consumed? Does that need >> to use holdable cursors here because in turn the transaction around the >> cursor could end sooner than expected? >> >> >> Jan >> > > This is an interesting question. The current logic for cursor backed > result sets will error if you try to access the result set after a > commit/rollback (actually you will get an error when you exhaust the > already fetched values that are in memory and try to fetch more from the > database). Only >= 7.4. Everything before just gives a NOTICE but otherwise just returns "FETCH 0" or "CLOSE" as if the cursor would exist. It does that even for cursors that have never been declared. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #