Patch to improve commit time performance and a few other things - Mailing list pgsql-jdbc

Attached is a patch that does the following:

1) improves performance of commit/rollback by reducing number of round
trips to the server
2) uses 7.1 functionality for setting the transaction isolation level
3) backs out a patch from 11 days ago because that code failed to
compile under jdk1.1

Details:

1)  The old code was doing the following for each commit:
   commit
   begin
   set transaction isolation level xxx
thus a call to commit was performing three round trips to the database.
  The new code does this in one round trip as:
   commit; begin; set transaction isolation level xxx

In a simple test program that performs 1000 transactions (where each
transaction does one simple select inside that transaction) has the
following before and after timings:

Client and Server on same machine
old         new
---         ---
1.877sec    1.405sec   25.1% improvement

Client and Server on different machines
old         new
---         ---
4.184sec    2.927sec   34.3% improvement

(all timings are an average of four different runs)


2)  The driver was using 'set transaction isolation level xxx' at the
begining of each transaction, instead of using the new 7.1 syntax of
'set session characteristics as transaction isolation level xxx' which
only needs to be done once instead of for each transaction.  This is
done conditionally (i.e. if server is 7.0 or older do the old behaviour,
else do the new behaviour) to not break backward compatibility.  This
also required the movement of some code to check/test database version
numbers from the DatabaseMetaData object to the Connection object.

3) Finally while testing, I discovered that the code that was checked in
  11 days ago actually didn't compile.  The code in the patch for
Connection.setCatalog() used Properties.setProperty() which only exists
in JDK1.2 or higher.  Thus compiling the JDBC1 driver failed as this
method doesn't exist.  Thus I backed out that patch.


thanks,
--Barry


*** ./interfaces/jdbc/org/postgresql/Connection.java.orig    Tue Jul 31 22:15:43 2001
--- ./interfaces/jdbc/org/postgresql/Connection.java    Wed Aug  1 20:34:17 2001
***************
*** 37,42 ****
--- 37,45 ----
     */
    private Encoding encoding = Encoding.defaultEncoding();

+   private String dbVersionLong;
+   private String dbVersionNumber;
+
    public boolean CONNECTION_OK = true;
    public boolean CONNECTION_BAD = false;

***************
*** 262,279 ****
        // used, so we denote this with 'UNKNOWN'.

        final String encodingQuery =
!       "select case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end";

        // Set datestyle and fetch db encoding in a single call, to avoid making
        // more than one round trip to the backend during connection startup.

        java.sql.ResultSet resultSet =
!       ExecSQL("set datestyle to 'ISO'; " + encodingQuery);

        if (! resultSet.next()) {
        throw new PSQLException("postgresql.con.failed", "failed getting backend encoding");
        }
!       dbEncoding = resultSet.getString(1);
        encoding = Encoding.getEncoding(dbEncoding, info.getProperty("charSet"));

        // Initialise object handling
--- 265,283 ----
        // used, so we denote this with 'UNKNOWN'.

        final String encodingQuery =
!       "case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end";

        // Set datestyle and fetch db encoding in a single call, to avoid making
        // more than one round trip to the backend during connection startup.

        java.sql.ResultSet resultSet =
!       ExecSQL("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");

        if (! resultSet.next()) {
        throw new PSQLException("postgresql.con.failed", "failed getting backend encoding");
        }
!       dbVersionLong = resultSet.getString(1);
!       dbEncoding = resultSet.getString(2);
        encoding = Encoding.getEncoding(dbEncoding, info.getProperty("charSet"));

        // Initialise object handling
***************
*** 904,911 ****
      if (autoCommit)
          ExecSQL("end");
      else {
!         ExecSQL("begin");
!         doIsolationLevel();
      }
      this.autoCommit = autoCommit;
      }
--- 908,914 ----
      if (autoCommit)
          ExecSQL("end");
      else {
!         ExecSQL("begin; " + getIsolationLevelSQL());
      }
      this.autoCommit = autoCommit;
      }
***************
*** 934,944 ****
      public void commit() throws SQLException {
      if (autoCommit)
          return;
!     ExecSQL("commit");
!     autoCommit = true;
!     ExecSQL("begin");
!     doIsolationLevel();
!     autoCommit = false;
      }

      /**
--- 937,943 ----
      public void commit() throws SQLException {
      if (autoCommit)
          return;
!     ExecSQL("commit; begin; " + getIsolationLevelSQL());
      }

      /**
***************
*** 952,962 ****
      public void rollback() throws SQLException {
      if (autoCommit)
          return;
!     ExecSQL("rollback");
!     autoCommit = true;
!     ExecSQL("begin");
!     doIsolationLevel();
!     autoCommit = false;
      }

      /**
--- 951,957 ----
      public void rollback() throws SQLException {
      if (autoCommit)
          return;
!     ExecSQL("rollback; begin; " + getIsolationLevelSQL());
      }

      /**
***************
*** 988,994 ****
      /**
       * You can call this method to try to change the transaction
       * isolation level using one of the TRANSACTION_* values.
!      *
       * <B>Note:</B> setTransactionIsolation cannot be called while
       * in the middle of a transaction
       *
--- 983,989 ----
      /**
       * You can call this method to try to change the transaction
       * isolation level using one of the TRANSACTION_* values.
!      *
       * <B>Note:</B> setTransactionIsolation cannot be called while
       * in the middle of a transaction
       *
***************
*** 999,1027 ****
       * @see java.sql.DatabaseMetaData#supportsTransactionIsolationLevel
       */
      public void setTransactionIsolation(int level) throws SQLException {
!     isolationLevel = level;
!     doIsolationLevel();
      }

      /**
       * Helper method used by setTransactionIsolation(), commit(), rollback()
!      * and setAutoCommit(). This sets the current isolation level.
!      */
!     protected void doIsolationLevel() throws SQLException {
      String q = "SET TRANSACTION ISOLATION LEVEL";

      switch(isolationLevel) {
          case java.sql.Connection.TRANSACTION_READ_COMMITTED:
!         ExecSQL(q + " READ COMMITTED");
!         return;

          case java.sql.Connection.TRANSACTION_SERIALIZABLE:
!         ExecSQL(q + " SERIALIZABLE");
!         return;

          default:
          throw new PSQLException("postgresql.con.isolevel",new Integer(isolationLevel));
      }
      }

      /**
--- 994,1060 ----
       * @see java.sql.DatabaseMetaData#supportsTransactionIsolationLevel
       */
      public void setTransactionIsolation(int level) throws SQLException {
!     //In 7.1 and later versions of the server it is possible using
!         //the "set session" command to set this once for all future txns
!         //however in 7.0 and prior versions it is necessary to set it in
!         //each transaction, thus adding complexity below.
!         //When we decide to drop support for servers older than 7.1
!         //this can be simplified
!         isolationLevel = level;
!         String isolationLevelSQL;
!     switch(isolationLevel) {
!         case java.sql.Connection.TRANSACTION_READ_COMMITTED:
!         if (haveMinimumServerVersion("7.1")) {
!           isolationLevelSQL = "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED";
!         } else {
!                   isolationLevelSQL = getIsolationLevelSQL();
!                 }
!                 break;
!
!         case java.sql.Connection.TRANSACTION_SERIALIZABLE:
!         if (haveMinimumServerVersion("7.1")) {
!           isolationLevelSQL = "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE";
!         } else {
!                   isolationLevelSQL = getIsolationLevelSQL();
!                 }
!                 break;
!
!         default:
!         throw new PSQLException("postgresql.con.isolevel",new Integer(isolationLevel));
!     }
!     ExecSQL(isolationLevelSQL);
      }

      /**
       * Helper method used by setTransactionIsolation(), commit(), rollback()
!      * and setAutoCommit(). This returns the SQL string needed to
!      * set the isolation level for a transaction.  In 7.1 and later it
!      * is possible to set a default isolation level that applies to all
!      * future transactions, this method is only necesary for 7.0 and older
!      * servers, and should be removed when support for these older
!      * servers are dropped
!      */
!     protected String getIsolationLevelSQL() throws SQLException {
!     //7.1 and higher servers have a default specified so
!     //no additional SQL is required to set the isolation level
!     if (haveMinimumServerVersion("7.1")) {
!           return "";
!         }
      String q = "SET TRANSACTION ISOLATION LEVEL";

      switch(isolationLevel) {
          case java.sql.Connection.TRANSACTION_READ_COMMITTED:
!         q = q + " READ COMMITTED";
!                 break;

          case java.sql.Connection.TRANSACTION_SERIALIZABLE:
!         q = q + " SERIALIZABLE";
!                 break;

          default:
          throw new PSQLException("postgresql.con.isolevel",new Integer(isolationLevel));
      }
+         return q;
      }

      /**
***************
*** 1033,1045 ****
       */
      public void setCatalog(String catalog) throws SQLException
      {
!     if(catalog!=null && !catalog.equals(PG_DATABASE)) {
!         close();
!         Properties info=new Properties();
!         info.setProperty("user", PG_USER);
!         info.setProperty("password", PG_PASSWORD);
!         openConnection(PG_HOST, PG_PORT, info, catalog, this_url, this_driver);
!     }
      }

      /**
--- 1066,1072 ----
       */
      public void setCatalog(String catalog) throws SQLException
      {
!       //no-op
      }

      /**
***************
*** 1095,1098 ****
--- 1122,1152 ----
        return sql;
      }

+   /**
+    * What is the version of the server
+    *
+    * @return the database version
+    * @exception SQLException if a database access error occurs
+    */
+   public String getDBVersionNumber() throws SQLException
+   {
+     if(dbVersionNumber == null) {
+       StringTokenizer versionParts = new StringTokenizer(dbVersionLong);
+       versionParts.nextToken(); /* "PostgreSQL" */
+       dbVersionNumber = versionParts.nextToken(); /* "X.Y.Z" */
+     }
+     return dbVersionNumber;
+   }
+
+   public boolean haveMinimumServerVersion(String ver) throws SQLException
+   {
+       if (getDBVersionNumber().compareTo(ver)>=0)
+       return true;
+       else
+       return false;
+   }
+
+
+
  }
+
*** ./interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java.orig    Wed Aug  1 20:14:50 2001
--- ./interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java    Wed Aug  1 20:17:53 2001
***************
*** 47,61 ****
    private static final byte defaultRemarks[]="no remarks".getBytes();


-   private boolean haveMinimumServerVersion(String ver) throws SQLException
-   {
-       if (getDatabaseProductVersion().compareTo(ver)>=0)
-       return true;
-       else
-       return false;
-   }
-
-
    public DatabaseMetaData(Connection conn)
    {
      this.connection = conn;
--- 47,52 ----
***************
*** 126,132 ****
     */
    public boolean nullsAreSortedHigh() throws SQLException
    {
!       return haveMinimumServerVersion("7.2");
    }

    /**
--- 117,123 ----
     */
    public boolean nullsAreSortedHigh() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.2");
    }

    /**
***************
*** 159,165 ****
     */
    public boolean nullsAreSortedAtEnd() throws SQLException
    {
!       return ! haveMinimumServerVersion("7.2");
    }

    /**
--- 150,156 ----
     */
    public boolean nullsAreSortedAtEnd() throws SQLException
    {
!       return ! connection.haveMinimumServerVersion("7.2");
    }

    /**
***************
*** 182,195 ****
     */
    public String getDatabaseProductVersion() throws SQLException
    {
!     java.sql.ResultSet resultSet = connection.ExecSQL("select version()");
!     resultSet.next();
!
!     StringTokenizer versionParts = new StringTokenizer(resultSet.getString(1));
!     versionParts.nextToken(); /* "PostgreSQL" */
!     String versionNumber = versionParts.nextToken(); /* "X.Y.Z" */
!
!     return versionNumber;
    }

    /**
--- 173,179 ----
     */
    public String getDatabaseProductVersion() throws SQLException
    {
!     return connection.getDBVersionNumber();
    }

    /**
***************
*** 558,564 ****
     */
    public boolean supportsOrderByUnrelated() throws SQLException
    {
!       return haveMinimumServerVersion("6.4");
    }

    /**
--- 542,548 ----
     */
    public boolean supportsOrderByUnrelated() throws SQLException
    {
!       return connection.haveMinimumServerVersion("6.4");
    }

    /**
***************
*** 581,587 ****
     */
    public boolean supportsGroupByUnrelated() throws SQLException
    {
!       return haveMinimumServerVersion("6.4");
    }

    /**
--- 565,571 ----
     */
    public boolean supportsGroupByUnrelated() throws SQLException
    {
!       return connection.haveMinimumServerVersion("6.4");
    }

    /**
***************
*** 608,614 ****
     */
    public boolean supportsLikeEscapeClause() throws SQLException
    {
!     return haveMinimumServerVersion("7.1");
    }

    /**
--- 592,598 ----
     */
    public boolean supportsLikeEscapeClause() throws SQLException
    {
!     return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 749,755 ****
     */
    public boolean supportsOuterJoins() throws SQLException
    {
!       return haveMinimumServerVersion("7.1");
    }

    /**
--- 733,739 ----
     */
    public boolean supportsOuterJoins() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 761,767 ****
     */
    public boolean supportsFullOuterJoins() throws SQLException
    {
!       return haveMinimumServerVersion("7.1");
    }

    /**
--- 745,751 ----
     */
    public boolean supportsFullOuterJoins() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 976,982 ****
     */
    public boolean supportsSelectForUpdate() throws SQLException
    {
!       return haveMinimumServerVersion("6.5");
    }

    /**
--- 960,966 ----
     */
    public boolean supportsSelectForUpdate() throws SQLException
    {
!       return connection.haveMinimumServerVersion("6.5");
    }

    /**
***************
*** 1053,1059 ****
     */
    public boolean supportsCorrelatedSubqueries() throws SQLException
    {
!       return haveMinimumServerVersion("7.1");
    }

    /**
--- 1037,1043 ----
     */
    public boolean supportsCorrelatedSubqueries() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 1075,1081 ****
     */
    public boolean supportsUnionAll() throws SQLException
    {
!       return haveMinimumServerVersion("7.1");
    }

    /**
--- 1059,1065 ----
     */
    public boolean supportsUnionAll() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 1303,1309 ****
     */
    public int getMaxRowSize() throws SQLException
    {
!       if (haveMinimumServerVersion("7.1"))
        return 1073741824;    // 1 GB
        else
        return 8192;        // XXX could be altered
--- 1287,1293 ----
     */
    public int getMaxRowSize() throws SQLException
    {
!       if (connection.haveMinimumServerVersion("7.1"))
        return 1073741824;    // 1 GB
        else
        return 8192;        // XXX could be altered
***************
*** 1329,1335 ****
     */
    public int getMaxStatementLength() throws SQLException
    {
!       if (haveMinimumServerVersion("7.0"))
        return 0;        // actually whatever fits in size_t
        else
        return 16384;
--- 1313,1319 ----
     */
    public int getMaxStatementLength() throws SQLException
    {
!       if (connection.haveMinimumServerVersion("7.0"))
        return 0;        // actually whatever fits in size_t
        else
        return 16384;
*** ./interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java.orig    Tue Jul 31 22:16:25 2001
--- ./interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java    Tue Jul 31 21:29:44 2001
***************
*** 47,61 ****
    private static final byte defaultRemarks[]="no remarks".getBytes();


-   private boolean haveMinimumServerVersion(String ver) throws SQLException
-   {
-       if (getDatabaseProductVersion().compareTo(ver)>=0)
-       return true;
-       else
-       return false;
-   }
-
-
    public DatabaseMetaData(Connection conn)
    {
      this.connection = conn;
--- 47,52 ----
***************
*** 126,132 ****
     */
    public boolean nullsAreSortedHigh() throws SQLException
    {
!       return haveMinimumServerVersion("7.2");
    }

    /**
--- 117,123 ----
     */
    public boolean nullsAreSortedHigh() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.2");
    }

    /**
***************
*** 159,165 ****
     */
    public boolean nullsAreSortedAtEnd() throws SQLException
    {
!       return ! haveMinimumServerVersion("7.2");
    }

    /**
--- 150,156 ----
     */
    public boolean nullsAreSortedAtEnd() throws SQLException
    {
!       return ! connection.haveMinimumServerVersion("7.2");
    }

    /**
***************
*** 182,195 ****
     */
    public String getDatabaseProductVersion() throws SQLException
    {
!     java.sql.ResultSet resultSet = connection.ExecSQL("select version()");
!     resultSet.next();
!
!     StringTokenizer versionParts = new StringTokenizer(resultSet.getString(1));
!     versionParts.nextToken(); /* "PostgreSQL" */
!     String versionNumber = versionParts.nextToken(); /* "X.Y.Z" */
!
!     return versionNumber;
    }

    /**
--- 173,179 ----
     */
    public String getDatabaseProductVersion() throws SQLException
    {
!     return connection.getDBVersionNumber();
    }

    /**
***************
*** 558,564 ****
     */
    public boolean supportsOrderByUnrelated() throws SQLException
    {
!       return haveMinimumServerVersion("6.4");
    }

    /**
--- 542,548 ----
     */
    public boolean supportsOrderByUnrelated() throws SQLException
    {
!       return connection.haveMinimumServerVersion("6.4");
    }

    /**
***************
*** 581,587 ****
     */
    public boolean supportsGroupByUnrelated() throws SQLException
    {
!       return haveMinimumServerVersion("6.4");
    }

    /**
--- 565,571 ----
     */
    public boolean supportsGroupByUnrelated() throws SQLException
    {
!       return connection.haveMinimumServerVersion("6.4");
    }

    /**
***************
*** 608,614 ****
     */
    public boolean supportsLikeEscapeClause() throws SQLException
    {
!     return haveMinimumServerVersion("7.1");
    }

    /**
--- 592,598 ----
     */
    public boolean supportsLikeEscapeClause() throws SQLException
    {
!     return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 749,755 ****
     */
    public boolean supportsOuterJoins() throws SQLException
    {
!       return haveMinimumServerVersion("7.1");
    }

    /**
--- 733,739 ----
     */
    public boolean supportsOuterJoins() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 761,767 ****
     */
    public boolean supportsFullOuterJoins() throws SQLException
    {
!       return haveMinimumServerVersion("7.1");
    }

    /**
--- 745,751 ----
     */
    public boolean supportsFullOuterJoins() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 976,982 ****
     */
    public boolean supportsSelectForUpdate() throws SQLException
    {
!       return haveMinimumServerVersion("6.5");
    }

    /**
--- 960,966 ----
     */
    public boolean supportsSelectForUpdate() throws SQLException
    {
!       return connection.haveMinimumServerVersion("6.5");
    }

    /**
***************
*** 1053,1059 ****
     */
    public boolean supportsCorrelatedSubqueries() throws SQLException
    {
!       return haveMinimumServerVersion("7.1");
    }

    /**
--- 1037,1043 ----
     */
    public boolean supportsCorrelatedSubqueries() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 1075,1081 ****
     */
    public boolean supportsUnionAll() throws SQLException
    {
!       return haveMinimumServerVersion("7.1");
    }

    /**
--- 1059,1065 ----
     */
    public boolean supportsUnionAll() throws SQLException
    {
!       return connection.haveMinimumServerVersion("7.1");
    }

    /**
***************
*** 1303,1309 ****
     */
    public int getMaxRowSize() throws SQLException
    {
!       if (haveMinimumServerVersion("7.1"))
        return 1073741824;    // 1 GB
        else
        return 8192;        // XXX could be altered
--- 1287,1293 ----
     */
    public int getMaxRowSize() throws SQLException
    {
!       if (connection.haveMinimumServerVersion("7.1"))
        return 1073741824;    // 1 GB
        else
        return 8192;        // XXX could be altered
***************
*** 1329,1335 ****
     */
    public int getMaxStatementLength() throws SQLException
    {
!       if (haveMinimumServerVersion("7.0"))
        return 0;        // actually whatever fits in size_t
        else
        return 16384;
--- 1313,1319 ----
     */
    public int getMaxStatementLength() throws SQLException
    {
!       if (connection.haveMinimumServerVersion("7.0"))
        return 0;        // actually whatever fits in size_t
        else
        return 16384;