Обсуждение: 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 #