Re: Getting "This ResultSet is closed" exceptions

I have some code that works most of the time, but I left it running
overnight and at 3am I got the "This ResultSet is closed" exception on
the "while (rs.next())" line in the following code:

ResultSet rs = getPerformancesOnVenueStmt.executeQuery();

while(rs.next())
{
Performance performance = parseResultSet(null, null, rs);
if (performance != null)
retList.add(performance);
}
rs.close();

The method "parseResultSet" does not close the result set, it just
does the various "rs.get..." calls and creates a Performance object.

Is it possible that another thread doing a commit on the same
Connection could cause this?

--
For my assured failures and derelictions I ask pardon beforehand of my
betters and my equals in my Calling here assembled, praying that in
the hour of my temptations, weakness and weariness, the memory of this
my Obligation and of the company before whom it was entered into, may
return to me to aid, comfort and restrain.

Another thread my close the result set if it's using the same
statement. Or the thread may close the connection, but i think that
would throw another exception. Other wise your code look right

On Feb 18, 2008 8:19 AM, Paul Tomblin <ptomblin(at)gmail(dot)com> wrote:
> I have some code that works most of the time, but I left it running
> overnight and at 3am I got the "This ResultSet is closed" exception on
> the "while (rs.next())" line in the following code:
>
> ResultSet rs = getPerformancesOnVenueStmt.executeQuery();
>
> while(rs.next())
> {
> Performance performance = parseResultSet(null, null, rs);
> if (performance != null)
> retList.add(performance);
> }
> rs.close();
>
> The method "parseResultSet" does not close the result set, it just
> does the various "rs.get..." calls and creates a Performance object.
>
> Is it possible that another thread doing a commit on the same
> Connection could cause this?
>
> --
> For my assured failures and derelictions I ask pardon beforehand of my
> betters and my equals in my Calling here assembled, praying that in
> the hour of my temptations, weakness and weariness, the memory of this
> my Obligation and of the company before whom it was entered into, may
> return to me to aid, comfort and restrain.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

On Feb 18, 2008 1:51 PM, Andres Olarte <olarte(dot)andres(at)gmail(dot)com> wrote:
> Another thread my close the result set if it's using the same
> statement. Or the thread may close the connection, but i think that
> would throw another exception. Other wise your code look right

Well, no other method is using the same PreparedStatement. So I guess
it's likely that another thread is doing the same query, and that's
what's closing the ResultSet. I guess it's time to make some of these
methods synchronized.

--
For my assured failures and derelictions I ask pardon beforehand of my
betters and my equals in my Calling here assembled, praying that in
the hour of my temptations, weakness and weariness, the memory of this
my Obligation and of the company before whom it was entered into, may
return to me to aid, comfort and restrain.

You might go for using a single connection per thread. Works for me.

On Feb 18, 2008 12:59 PM, Paul Tomblin <ptomblin(at)gmail(dot)com> wrote:
> On Feb 18, 2008 1:51 PM, Andres Olarte <olarte(dot)andres(at)gmail(dot)com> wrote:
> > Another thread my close the result set if it's using the same
> > statement. Or the thread may close the connection, but i think that
> > would throw another exception. Other wise your code look right
>
> Well, no other method is using the same PreparedStatement. So I guess
> it's likely that another thread is doing the same query, and that's
> what's closing the ResultSet. I guess it's time to make some of these
> methods synchronized.
>
> --
> For my assured failures and derelictions I ask pardon beforehand of my
> betters and my equals in my Calling here assembled, praying that in
> the hour of my temptations, weakness and weariness, the memory of this
> my Obligation and of the company before whom it was entered into, may
> return to me to aid, comfort and restrain.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

On Feb 18, 2008 2:10 PM, Andres Olarte <olarte(dot)andres(at)gmail(dot)com> wrote:
> You might go for using a single connection per thread. Works for me.

Forgive a possibly stupid question, but how would a class know whether
there is a Connection for this thread already? I use a simple static
to hold the Connection, and so I get one for the whole program. But
there is a lot of asynchronous stuff going on with GUI callbacks,
external "messages" and RMI calls, etc. Do I have to create and
destroy a connection in every callback, or use some sort of thread
pooling system? A previous engineer on this project had one subsystem
that was creating a new database connection every second, and then
closing it a few milliseconds later - that seems like madness to me.

--
For my assured failures and derelictions I ask pardon beforehand of my
betters and my equals in my Calling here assembled, praying that in
the hour of my temptations, weakness and weariness, the memory of this
my Obligation and of the company before whom it was entered into, may
return to me to aid, comfort and restrain.

On Monday 18 February 2008 14:48:24 Paul Tomblin wrote:
> Forgive a possibly stupid question, but how would a class know whether
> there is a Connection for this thread already?  I use a simple static
> to hold the Connection, and so I get one for the whole program.

That sounds like a bad plan to me. You're going to interleave transactions
etc. (You are using transactions, aren't you? :) Look into connection pools
(apache has DBCP http://commons.apache.org/dbcp which is good), or otherwise
use a threadlocal that keeps a connection per thread around.

> But
> there is a lot of asynchronous stuff going on with GUI callbacks,
> external "messages" and RMI calls, etc.  Do I have to create and
> destroy a connection in every callback, or use some sort of thread
> pooling system?  A previous engineer on this project had one subsystem
> that was creating a new database connection every second, and then
> closing it a few milliseconds later - that seems like madness to me

Creative, but not very scalable :)

jan

--
--------------------------------------------------------------
Jan de Visser                     jdevisser(at)digitalfairway(dot)com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

On Feb 18, 2008 5:08 PM, Jan de Visser <jdevisser(at)digitalfairway(dot)com> wrote:
> On Monday 18 February 2008 14:48:24 Paul Tomblin wrote:
> > Forgive a possibly stupid question, but how would a class know whether
> > there is a Connection for this thread already? I use a simple static
> > to hold the Connection, and so I get one for the whole program.
>
> That sounds like a bad plan to me. You're going to interleave transactions
> etc. (You are using transactions, aren't you? :) Look into connection pools

Unfortunately the existing code ran with autocommit on, and there was
no concern for transations. I've turned off autocommit, and am
running around the code trying to figure out where is the best place
to put "conn.commit();" calls. Right now, I'm hunting down a lot of
cases where one program or another is stuck because of transaction
locks held by another, so I'm being overzealous and sprinkling them
around liberally, but I think we definitely need them before we send
an event or an RMI call, or before finishing a thread or sleeping, but
otherwise we probably don't need any others.

I also run a "vacuumdb -analyze" every night, and that's causing
problems in the field with lock contention[1]. That's one reason why
I want to make sure we use transactions and commit when we need to.

[1] I know it's off topic for this mailing list, but can anybody point
me to a site that shows how to figure out what Java program is causing
the thread locks? Right now I'm doing it like:
ps auwwx | grep 'idle in transaction'
that gives me the socket number, and then I use
lsof -i :[socketnumber]
and that gives me the two PIDs that have it open, one of which is
postgresql and the other is java, and then I go back to "ps auwwx" to
find that the arguments to "java" for that pid to find out what
program it is.

--
For my assured failures and derelictions I ask pardon beforehand of my
betters and my equals in my Calling here assembled, praying that in
the hour of my temptations, weakness and weariness, the memory of this
my Obligation and of the company before whom it was entered into, may
return to me to aid, comfort and restrain.

On 18-Feb-08, at 2:48 PM, Paul Tomblin wrote:

> On Feb 18, 2008 2:10 PM, Andres Olarte <olarte(dot)andres(at)gmail(dot)com>
> wrote:
>> You might go for using a single connection per thread. Works for me.
>
> Forgive a possibly stupid question, but how would a class know whether
> there is a Connection for this thread already? I use a simple static
> to hold the Connection, and so I get one for the whole program. But
> there is a lot of asynchronous stuff going on with GUI callbacks,
> external "messages" and RMI calls, etc. Do I have to create and
> destroy a connection in every callback, or use some sort of thread
> pooling system? A previous engineer on this project had one subsystem
> that was creating a new database connection every second, and then
> closing it a few milliseconds later - that seems like madness to me.

And it was/is madness. Yes you want pooling mechanism, and I don't
think a single connection for the whole program is a good thing.
Certainly if you have async callbacks it's very likely you are
stomping on statements.

Dave
>
>
>
> --
> For my assured failures and derelictions I ask pardon beforehand of my
> betters and my equals in my Calling here assembled, praying that in
> the hour of my temptations, weakness and weariness, the memory of this
> my Obligation and of the company before whom it was entered into, may
> return to me to aid, comfort and restrain.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

On Monday 18 February 2008 18:07:59 Paul Tomblin wrote:
>  I've turned off autocommit, and am
> running around the code trying to figure out where is the best place
> to put "conn.commit();" calls.

Come again? You are using a single connection and are calling commit and
seemingly random spots? That means that thread A will call commit while
thread B is in the middle of doing things.

You are applying liberal amounts of lipstick on an animal that to everybody
looks conspicuously like a porcine. Do yourself a favour. Introduce a
connection pool before you (or worse, your customers) go insane.

jan

--
--------------------------------------------------------------
Jan de Visser                     jdevisser(at)digitalfairway(dot)com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

Jan de Visser wrote:
> On Monday 18 February 2008 18:07:59 Paul Tomblin wrote:
>> I've turned off autocommit, and am
>> running around the code trying to figure out where is the best place
>> to put "conn.commit();" calls.
>
> Come again? You are using a single connection and are calling commit and
> seemingly random spots? That means that thread A will call commit while
> thread B is in the middle of doing things.

Like I said, it's been running in autocommit mode for about 6 years now. Any
problems that might have occurred from an inconsistent database have been seen
and rectified long ago.

--
Paul Tomblin <ptomblin(at)xcski(dot)com> http://blog.xcski.com/
Sept 25th: Discovered lots of things about Dynamic HTML.
Notably that almost every site attempting to use it is crap.
-- Alan Cox's diary