Re: commit and rollback don't throw exceptions when theyshould

Oliver Jowett <oliver@opencloud.com> wrote:

>> Drivers other than PosrgreSQL throws exception as I had expected,
>> but PostgreSQL didn't throw exception.
>
> Yes, that is the case I mentioned where there have been no commands
> executed in the transaction. In that case, the driver has no work to
> do on commit() so it does not notice the connection was closed.
>
> Even if we added a check for an explicit Connection.close() call
> before Connection.commit(), that would not help with your test where
> you are shutting down the server. The driver will only notice the
> network connection is gone when it next tries to actually send or
> receive data.

A connection is allowed to be flagged as closed if a fatal error is
found; and in fact it seems that the PostgreSQL JDBC driver does this.
With the attached WIP patch (comments and documentation will need
attention, if nothing else), the problem seems corrected, based on
this Java reworking of groovy script.

import java.sql.*;
public final class ConnectionTest
{
    static final String DRIVER_CLASS_NAME = "org.postgresql.Driver";
    static final String URL = "jdbc:postgresql:test?user=kgrittn";
    public static void main(String[] args) throws Exception {
        Class driverClass = Class.forName(DRIVER_CLASS_NAME);
        Connection con = DriverManager.getConnection(URL);
        con.setAutoCommit(false);
        System.out.print("Restart the DB server and press any key.");
        System.in.read();
        PreparedStatement ps =
            con.prepareStatement("insert into foo values (?)");
        ps.setInt(1, 1);
        try {
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                System.out.println("Attempting rollback.");
                con.rollback();
            } catch (SQLException rollbackFailed) {
                rollbackFailed.printStackTrace();
            }
        }
        con.close();
    }
}

With or without a restart I get no exception on the rollback attempt
following the failure of the ps.executeUpdate() line.  With this
patch, the rollback attempt throws this:

org.postgresql.util.PSQLException: Cannot rollback when connection is
closed.
        at
org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(
AbstractJdbc2Connection.java:710)
        at ConnectionTest.main(ConnectionTest.java:29)

I think this should solve the problem for any reasonable pooler
implementation.  Does anyone disagree or see a problem with this
approach?  (If not, I'll try to polish it up.)

-Kevin