Thread: BUG #5127: AbstractJdbc2Connection#doRollback should throws Exception if connection is closed

The following bug has been logged online:

Bug reference:      5127
Logged by:
Email address:      taktos@gmail.com
PostgreSQL version: 8.3.7
Operating system:   CentOS 5.1
Description:        AbstractJdbc2Connection#doRollback should throws
Exception if connection is closed
Details:

If PostgreSQL server is restarted, old Connection pooled in Application
server's ConnectionPool cannot connect to DB.
That's OK.
But, I can call rollback() on old Connection and it throws no exception.
(I've set autocommit to false and ProtocolConnection#getTransactionState
returns 0)

It's fault. Rollback() should fail (throw exception) because JDBC connection
could not execute rollback.

<taktos@gmail.com> wrote:

> If PostgreSQL server is restarted, old Connection pooled in
> Application server's ConnectionPool cannot connect to DB.
> That's OK.
> But, I can call rollback() on old Connection and it throws no
> exception.

Hmmm....   What problem are you having?  The transaction would have
been rolled back when the server was restarted (or if the connection
was broken).  What benefit would you get from the exception you
suggest?

-Kevin

takiguchi <taktos@gmail.com> wrote:

> This is a problem of connection pooling, not of transaction.
>
> public void testConnection() {
>    Connection con = dataSource.getConnection(); // get a connection
> from pool (If DB server restarted, invalid connection will be
> returned)
>    boolean valid = true;
>    try {
>        // execute some DMLs...
>        con.commit();
>    } catch (SQLException e) {
>        try {
>            con.rollback();
>        } catch (SQLException e) {
>            valid = false; // UNREACHABLE
>        }
>    } finally {
>        if (valid) {
>            con.close(); // Connection#close() doesn't close
> connection in reality in connection pooling mechanism. It simply
> returns the connection to pool.
>        }
>    }
> }
>
> Because rollback() throws no exception when physical connection has
> been closed, I cannot know whether rollback was successfully
> completed.
> In general, if rollback() throws NO exception, it must be a success.
> (Success means rollback process is executed, and ended successfully.
> This represents that connection could access to server.)
>
> I think if physical connection has closed, Connection's method
> should fail and throws Exception.

That's sort of an odd construct, but I see your point.  Checking the
javadocs, I see that they very explicitly support your position.

http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#rollback%28%29

| SQLException - if a database access error occurs, this method is
| called while participating in a distributed transaction, this method
| is called on a closed connection or this Connection object is in
| auto-commit mode

This is indeed a bug.  No doubt about it.

Since it is a JDBC driver bug, it might be best to post to that list,
with a reference back to this thread.  Do you want to put together a
JDBC driver patch, or should I?

-Kevin

takiguchi <taktos@gmail.com> wrote:

> public void testConnection() {
>    Connection con = dataSource.getConnection(); // get a connection
> from pool (If DB server restarted, invalid connection will be
> returned)
>    boolean valid = true;
>    try {
>        // execute some DMLs...
>        con.commit();
>    } catch (SQLException e) {
>        try {
>            con.rollback();
>        } catch (SQLException e) {
>            valid = false; // UNREACHABLE
>        }
>    } finally {
>        if (valid) {
>            con.close(); // Connection#close() doesn't close
> connection in reality in connection pooling mechanism. It simply
> returns the connection to pool.
>        }
>    }
> }

I'm looking at the JDBC driver, and so far I can't see why a rollback
attempt wouldn't generate a SQLException when the commit attempt did
so for a broken connection.

Is it possible that you have autoCommit set to true?  The driver is
currently skipping the commit or rollback attempts when that is true,
which is improper; but I'm not sure you're going to be very happy with
the above code if we make it behave like the Sun javadocs require,
either.  With autoCommit set to true, *any* commit or rollback attempt
should throw an exception, so in that case the above code would never
return a connection to the pool, nor would it close the connection
properly.

This makes me concerned that fixing the bug in the JDBC driver could
expose serious bugs in application code, and break things which are
currently working, for some values of "working".  :-(

-Kevin

Hi, Kevin.
Thank you for your reply.

This is a problem of connection pooling, not of transaction.

public void testConnection() {
   Connection con = dataSource.getConnection(); // get a connection
from pool (If DB server restarted, invalid connection will be
returned)
   boolean valid = true;
   try {
       // execute some DMLs...
       con.commit();
   } catch (SQLException e) {
       try {
           con.rollback();
       } catch (SQLException e) {
           valid = false; // UNREACHABLE
       }
   } finally {
       if (valid) {
           con.close(); // Connection#close() doesn't close
connection in reality in connection pooling mechanism. It simply
returns the connection to pool.
       }
   }
}

Because rollback() throws no exception when physical connection has
been closed, I cannot know whether rollback was successfully
completed.
In general, if rollback() throws NO exception, it must be a success.
(Success means rollback process is executed, and ended successfully.
This represents that connection could access to server.)

I think if physical connection has closed, Connection's method should
fail and throws Exception.

Other JDBC Drivers (like Oracle) throws Exception when Connection's
method (rollback, prepareStatement, etc.) was called and physical
connection had been closed.


Sincerely,
takiguchi

> Is it possible that you have autoCommit set to true?

No, it is impossible as you know. Sample code I wrote is just a sample.
Actually I use a DI container (Seasar, it is a popular DI container in
Japan), and Seasar provides DBCP implementation.

 > This makes me concerned that fixing the bug in the JDBC driver could
 > expose serious bugs in application code, and break things which are
 > currently working, for some values of "working".  :-(

Indeed. But on the other hand, it is serious problem that PostgreSQL
doesn't behave like other JDBC drivers.

On a related note, JDK 6's JDBC is based on JDBC 4.0 spec.
I see JDK 5's, and I found a bit of difference to 6's.

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html#rollback()

| SQLException - if a database access error occurs or this Connection
| object is in auto-commit mode

There is no explicit spec about use of closed connection!
Because PostgreSQL's JDBC Driver is based on JDBC 3 specification, it
maybe that this is not a bug.

I'm still thinking that this is driver's bug. But if PostgreSQL team
decides that it's not a bug and won't fix, I accept the inevitable. And
I wait for a version compliant for JDBC 4.0.

Regards,
takiguchi