Re: Multiple open ResultSets not allowed?

It seems that connection.commit() commits current transaction and immediately begins a new one so that connection status is always "idle in transaction". During that time no indicies could be created/dropped, no vaccum command can be issued etc because of locks I suppose.
I use PostgreSQL 7.1 and tried 7.2 drivers and 7.3 drivers (with option compatible=7.1).
Is there anything that can be done to allow creating index without restarting the connection pool?

I have just switched to using the 7.3 JDBC driver and am no longer
allowed to have multiple ResultSets open at the same time.

When running code that iterates through two open ResultSets (from two
different Statements from the same Connection), the following
exception is thrown:

Connection is closed. Operation is not permitted.
at org.postgresql.jdbc1.AbstractJdbc1ResultSet.next
(AbstractJdbc1ResultSet.java:92)

I haven't looked at the AbstractJdbc1ResultSet code to see what is
going on, but according to the JDBC Javadocs, "if the reading of one
ResultSet object is interleaved with the reading of another, each
must have been generated by different Statement objects." Therefore,
the code I am executing should not throw this exception, and with the
previous version of the driver it did not.

Is this a known bug? Has it been fixed? Did I misinterpret the Javadocs?

Thanks.

Jeff

The error message says that the Connection is closed. You can't use a
statement or result set after the connection that owns them is closed.
This is part of the jdbc spec, and I think logic in the driver was
tightened up in this area in 7.3.

thanks,
--Barry

Jeff Kolesky wrote:
> I have just switched to using the 7.3 JDBC driver and am no longer
> allowed to have multiple ResultSets open at the same time.
>
> When running code that iterates through two open ResultSets (from two
> different Statements from the same Connection), the following exception
> is thrown:
>
> Connection is closed. Operation is not permitted.
> at org.postgresql.jdbc1.AbstractJdbc1ResultSet.next
> (AbstractJdbc1ResultSet.java:92)
>
> I haven't looked at the AbstractJdbc1ResultSet code to see what is going
> on, but according to the JDBC Javadocs, "if the reading of one ResultSet
> object is interleaved with the reading of another, each must have been
> generated by different Statement objects." Therefore, the code I am
> executing should not throw this exception, and with the previous version
> of the driver it did not.
>
> Is this a known bug? Has it been fixed? Did I misinterpret the Javadocs?
>
> Thanks.
>
> Jeff
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Alexey,

A transaction being started doesn't prevent you from creating an index.
A lock on a table prevents you from creating an index. A select
statement will cause locks on the table it selects from and those locks
are not released until the transaction is committed. What I suspect is
happening in your application (I say this from experience because I
needed to do a lot of work in my application to avoid this) is the
following:

You have code that is getting a connection from your connection pool and
using it only for select operations. And because you are only doing
selects you are not commiting or rolling back before returning the
connection. But because you aren't commiting or rollingback the locks
the select aquired are still being held and thus indexes can't be
created, vacuum full can't be run, etc.

You need to make sure that you always commit/rollback before returning a
connection to your connection pool (or make sure your connection pool
does that for you).

thanks,
--Barry

Alexey Yudichev wrote:
> It seems that connection.commit() commits current transaction and immediately begins a new one so that connection status is always "idle in transaction". During that time no indicies could be created/dropped, no vaccum command can be issued etc because of locks I suppose.
> I use PostgreSQL 7.1 and tried 7.2 drivers and 7.3 drivers (with option compatible=7.1).
> Is there anything that can be done to allow creating index without restarting the connection pool?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Just to make sure I wasn't looking at the code incorrectly, I wrote
the following test code, making sure not to close the connection
before trying to get data from the result set:

Connection con = DB.getConnection();
Statement s1 = con.createStatement();
ResultSet rs1 = s1.executeQuery("SELECT * FROM table1");
while(rs1.next())
{
String col = rs1.getString("col");
System.out.println(col);
Statement s2 = con.createStatement();
ResultSet rs2 = s2.executeQuery("SELECT * FROM table2");
while(rs2.next())
{
String col2 = rs2.getString("col");
System.out.println("\t" + col2);
}
rs2.close();
s2.close();
}
rs1.close();
s1.close();
con.close();

Running this code throws the same exception. Looks like the
connection is being closed incorrectly by the driver, or more likely
that ResultSet data (Vector rows) is being set to null somehow.

Any other ideas?

Thanks.

Jeff

At 7:55 PM -0800 3/14/03, Barry Lind wrote:
>The error message says that the Connection is closed. You can't use
>a statement or result set after the connection that owns them is
>closed. This is part of the jdbc spec, and I think logic in the
>driver was tightened up in this area in 7.3.
>
>thanks,
>--Barry
>
>
>Jeff Kolesky wrote:
>>I have just switched to using the 7.3 JDBC driver and am no longer
>>allowed to have multiple ResultSets open at the same time.
>>
>>When running code that iterates through two open ResultSets (from
>>two different Statements from the same Connection), the following
>>exception is thrown:
>>
>> Connection is closed. Operation is not permitted.
>> at org.postgresql.jdbc1.AbstractJdbc1ResultSet.next
>>(AbstractJdbc1ResultSet.java:92)
>>
>>I haven't looked at the AbstractJdbc1ResultSet code to see what is
>>going on, but according to the JDBC Javadocs, "if the reading of
>>one ResultSet object is interleaved with the reading of another,
>>each must have been generated by different Statement objects."
>>Therefore, the code I am executing should not throw this exception,
>>and with the previous version of the driver it did not.
>>
>>Is this a known bug? Has it been fixed? Did I misinterpret the Javadocs?
>>
>>Thanks.
>>
>>Jeff
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>>http://archives.postgresql.org
>>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

Jeff Kolesky wrote:
> Just to make sure I wasn't looking at the code incorrectly, I wrote the
> following test code, making sure not to close the connection before
> trying to get data from the result set:
>
> Connection con = DB.getConnection();
> Statement s1 = con.createStatement();
> ResultSet rs1 = s1.executeQuery("SELECT * FROM table1");
> while(rs1.next())
> {
> String col = rs1.getString("col");
> System.out.println(col);
> Statement s2 = con.createStatement();
> ResultSet rs2 = s2.executeQuery("SELECT * FROM table2");
> while(rs2.next())
> {
> String col2 = rs2.getString("col");
> System.out.println("\t" + col2);
> }
> rs2.close();
> s2.close();
> }
> rs1.close();
> s1.close();
> con.close();
>
> Running this code throws the same exception. Looks like the connection
> is being closed incorrectly by the driver, or more likely that ResultSet
> data (Vector rows) is being set to null somehow.

All,
Something definitely seems to be resetting rows to null when a second
ResultSet is created on a Connection where another already exists. We
see this problem when we request a second rs (differing only by an ORDER
BY from the original) in a UI app we have. Inserting an initialization:

//don't know how this happens, but...
if (rows == null)
{
rows = new Vector();
}

in the code for AbstractJdbc2ResultSet#absolute(int index) cured a null
pointer exception, but of course this is completely unsatisfactory.
Haven't had a chance to track down where this var gets set yet...

Rich Cullingford
rculling(at)sysd(dot)com

Jeff,

I run this code using the latest 7.3 driver and don't have any problems.

--Barry

Jeff Kolesky wrote:
> Just to make sure I wasn't looking at the code incorrectly, I wrote the
> following test code, making sure not to close the connection before
> trying to get data from the result set:
>
> Connection con = DB.getConnection();
> Statement s1 = con.createStatement();
> ResultSet rs1 = s1.executeQuery("SELECT * FROM table1");
> while(rs1.next())
> {
> String col = rs1.getString("col");
> System.out.println(col);
> Statement s2 = con.createStatement();
> ResultSet rs2 = s2.executeQuery("SELECT * FROM table2");
> while(rs2.next())
> {
> String col2 = rs2.getString("col");
> System.out.println("\t" + col2);
> }
> rs2.close();
> s2.close();
> }
> rs1.close();
> s1.close();
> con.close();
>
> Running this code throws the same exception. Looks like the connection
> is being closed incorrectly by the driver, or more likely that ResultSet
> data (Vector rows) is being set to null somehow.
>
> Any other ideas?
>
> Thanks.
>
> Jeff
>
>
> At 7:55 PM -0800 3/14/03, Barry Lind wrote:
>
>> The error message says that the Connection is closed. You can't use a
>> statement or result set after the connection that owns them is closed.
>> This is part of the jdbc spec, and I think logic in the driver was
>> tightened up in this area in 7.3.
>>
>> thanks,
>> --Barry
>>
>>
>> Jeff Kolesky wrote:
>>
>>> I have just switched to using the 7.3 JDBC driver and am no longer
>>> allowed to have multiple ResultSets open at the same time.
>>>
>>> When running code that iterates through two open ResultSets (from two
>>> different Statements from the same Connection), the following
>>> exception is thrown:
>>>
>>> Connection is closed. Operation is not permitted.
>>> at org.postgresql.jdbc1.AbstractJdbc1ResultSet.next
>>> (AbstractJdbc1ResultSet.java:92)
>>>
>>> I haven't looked at the AbstractJdbc1ResultSet code to see what is
>>> going on, but according to the JDBC Javadocs, "if the reading of one
>>> ResultSet object is interleaved with the reading of another, each
>>> must have been generated by different Statement objects." Therefore,
>>> the code I am executing should not throw this exception, and with the
>>> previous version of the driver it did not.
>>>
>>> Is this a known bug? Has it been fixed? Did I misinterpret the
>>> Javadocs?
>>>
>>> Thanks.
>>>
>>> Jeff
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: Have you searched our list archives?
>>>
>>> http://archives.postgresql.org
>>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>