Re: Patch to improve commit time performance and a few other things

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

> 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(at)postgresql(dot)org

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