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

Patch applied.  Thanks.

> 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;

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026