Two patches
От | Dmitry Tkach |
---|---|
Тема | Two patches |
Дата | |
Msg-id | b3o6mq$2c35$1@news.hub.org обсуждение исходный текст |
Список | pgsql-patches |
Hi, everybody! Attached are two patches for the JDBC sources - comp_err_patch.txt fixes - a compilation error that I sent a message about earlier (but never got any reply for some reason): (it fails the same way in both REL7_3_STABLE and HEAD sources) > Buildfile: ./build.xml > > all: > > prepare: > [mkdir] Created dir: /home/dima/postgres7.3/pgsql/src/interfaces/jdbc/build > [mkdir] Created dir: /home/dima/postgres7.3/pgsql/src/interfaces/jdbc/jars > > check_versions: > > driver: > [copy] Copying 1 file to /home/dima/postgres7.3/pgsql/src/interfaces/jdbc/org/postgresql > [echo] Configured build for the JDBC3 edition driver > > compile: > [javac] Compiling 52 source files to /home/dima/postgres7.3/pgsql/src/interfaces/jdbc/build > [javac] /home/dima/postgres7.3/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java:560: incompatibletypes > [javac] found : java.sql.PreparedStatement > [javac] required: org.postgresql.jdbc2.PreparedStatement > [javac] deleteStatement = ((java.sql.Connection) connection).prepareStatement(deleteSQL.toString()); > [javac] ^ > [javac] /home/dima/postgres7.3/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java:619: incompatibletypes > [javac] found : java.sql.PreparedStatement > [javac] required: org.postgresql.jdbc2.PreparedStatement > [javac] insertStatement = ((java.sql.Connection) connection).prepareStatement(insertSQL.toString()); > [javac] ^ > [javac] /home/dima/postgres7.3/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java:955: incompatibletypes > [javac] found : java.sql.PreparedStatement > [javac] required: org.postgresql.jdbc2.PreparedStatement > [javac] selectStatement = ((java.sql.Connection) connection).prepareStatement(selectSQL.toString()); > [javac] ^ > [javac] /home/dima/postgres7.3/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java:1038: incompatibletypes > [javac] found : java.sql.PreparedStatement > [javac] required: org.postgresql.jdbc2.PreparedStatement > [javac] updateStatement = ((java.sql.Connection) connection).prepareStatement(updateSQL.toString()); > [javac] ^ > [javac] /home/dima/postgres7.3/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Statement.java:11: org.postgresql.jdbc2.Jdbc2Statementshould be declared abstract; it does not define getMoreResults(int) in org.postgresql.jdbc2.Jdbc2Statement > [javac] public class Jdbc2Statement extends org.postgresql.jdbc2.AbstractJdbc2Statement implements java.sql.Statement > The other one - timestamp_perf_patch.txt Fixes some performance problem I ran into, that has to do with parsing Timestamps - I was running a query that returned lots (like, a few thousand) rows, and it was taking much longer than I would expect. After some debugging and profiling, I wassurprised to find out, that *literally* most of the time was spent inside "new SimpleDateFormat()"... So, I made those formats to only be created ones, and stored in a static field. That made that unfortunate query about 80%quicker. I was also able to see some accross the board performance improvements (although, not that dramatic, because normally myqueries would only return a handful of rows, so the time wasted on creating those formats would be less noticable). So, I am sending it your way in case you like what it does, and want to put it in... Let me know if you see any problems with it. Thanks! Dima Index: AbstractJdbc1ResultSet.java =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java,v retrieving revision 1.7.2.1 diff -C10 -r1.7.2.1 AbstractJdbc1ResultSet.java *** AbstractJdbc1ResultSet.java 2003/01/14 09:15:35 1.7.2.1 --- AbstractJdbc1ResultSet.java 2003/02/28 17:27:00 *************** *** 34,53 **** --- 34,59 ---- protected SQLWarning warnings = null; // The warning chain protected boolean wasNullFlag = false; // the flag for wasNull() // We can chain multiple resultSets together - this points to // next resultSet in the chain. protected ResultSet next = null; protected StringBuffer sbuf = null; public byte[][] rowBuffer = null; + protected static final SimpleDateFormat tsFormat = + new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss"); + protected static final SimpleDateFormat tstzFormat = + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z"); + protected static final SimpleDateFormat dateFormat = + new SimpleDateFormat("yyyy-MM-dd"); public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn, Statement statement, Field[] fields, Vector tuples,String status, int updateCount, long insertOID, boolean binaryCursor) { this.connection = conn; this.statement = statement; this.fields = fields; this.rows = tuples; this.status = status; this.updateCount = updateCount; this.insertOID = insertOID; *************** *** 947,1023 **** rs.sbuf.append(" GMT"); rs.sbuf.append(c); rs.sbuf.append(s.substring(i, slen)); // Lastly, if the tz part doesn't specify the :MM part then // we add ":00" for java. if (slen - i < 5) rs.sbuf.append(":00"); // we'll use this dateformat string to parse the result. ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z"); } else { // Just found fractional seconds but no timezone. //If timestamptz then we use GMT, else local timezone if (pgDataType.equals("timestamptz")) { rs.sbuf.append(" GMT"); ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z"); } else { ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } } } else if (slen == 19) { // No tz or fractional second info. //If timestamptz then we use GMT, else local timezone if (pgDataType.equals("timestamptz")) { rs.sbuf.append(" GMT"); ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z"); } else { ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } } else { if (slen == 8 && s.equals("infinity")) //java doesn't have a concept of postgres's infinity //so set to an arbitrary future date s = "9999-01-01"; if (slen == 9 && s.equals("-infinity")) //java doesn't have a concept of postgres's infinity //so set to an arbitrary old date s = "0001-01-01"; // We must just have a date. This case is // needed if this method is called on a date // column ! df = new SimpleDateFormat("yyyy-MM-dd"); } try { // All that's left is to parse the string and return the ts. if ( org.postgresql.Driver.logDebug ) org.postgresql.Driver.debug("the data after parsing is " + rs.sbuf.toString() + " with " + nanos + " nanos"); ! Timestamp result = ! new Timestamp(df.parse(rs.sbuf.toString()).getTime()); result.setNanos(nanos); return result; } catch (ParseException e) { throw new PSQLException("postgresql.res.badtimestamp", new Integer(e.getErrorOffset()), s); } } } --- 953,1035 ---- rs.sbuf.append(" GMT"); rs.sbuf.append(c); rs.sbuf.append(s.substring(i, slen)); // Lastly, if the tz part doesn't specify the :MM part then // we add ":00" for java. if (slen - i < 5) rs.sbuf.append(":00"); // we'll use this dateformat string to parse the result. ! df = tstzFormat; } else { // Just found fractional seconds but no timezone. //If timestamptz then we use GMT, else local timezone if (pgDataType.equals("timestamptz")) { rs.sbuf.append(" GMT"); ! df = tstzFormat; } else { ! df = tsFormat; } } } else if (slen == 19) { // No tz or fractional second info. //If timestamptz then we use GMT, else local timezone if (pgDataType.equals("timestamptz")) { rs.sbuf.append(" GMT"); ! df = tstzFormat; } else { ! df = tsFormat; } } else { if (slen == 8 && s.equals("infinity")) //java doesn't have a concept of postgres's infinity //so set to an arbitrary future date s = "9999-01-01"; if (slen == 9 && s.equals("-infinity")) //java doesn't have a concept of postgres's infinity //so set to an arbitrary old date s = "0001-01-01"; // We must just have a date. This case is // needed if this method is called on a date // column ! df = dateFormat; } try { // All that's left is to parse the string and return the ts. if ( org.postgresql.Driver.logDebug ) org.postgresql.Driver.debug("the data after parsing is " + rs.sbuf.toString() + " with " + nanos + " nanos"); ! java.util.Date date = null; ! ! synchronized (df) ! { ! date = df.parse (rs.sbuf.toString ()); ! } ! ! Timestamp result = new Timestamp(date.getTime()); result.setNanos(nanos); return result; } catch (ParseException e) { throw new PSQLException("postgresql.res.badtimestamp", new Integer(e.getErrorOffset()), s); } } } Index: AbstractJdbc2ResultSet.java =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v retrieving revision 1.10.2.1 diff -C10 -r1.10.2.1 AbstractJdbc2ResultSet.java *** AbstractJdbc2ResultSet.java 2003/02/24 16:38:25 1.10.2.1 --- AbstractJdbc2ResultSet.java 2003/02/28 17:26:42 *************** *** 506,525 **** --- 506,531 ---- throws SQLException { if (doingUpdates) { doingUpdates = false; clearRowBuffer(); } } + protected PreparedStatement mkStatement (String sql) + throws SQLException + { + return (PreparedStatement) ((java.sql.Connection) connection). + prepareStatement(sql); + } public synchronized void deleteRow() throws SQLException { if ( !isUpdateable() ) { throw new PSQLException( "postgresql.updateable.notupdateable" ); } if (onInsertRow) *************** *** 550,570 **** for ( int i = 0; i < numKeys; i++ ) { deleteSQL.append( ((PrimaryKey) primaryKeys.get(i)).name ).append( " = ? " ); if ( i < numKeys - 1 ) { deleteSQL.append( " and " ); } } ! deleteStatement = ((java.sql.Connection) connection).prepareStatement(deleteSQL.toString()); } deleteStatement.clearParameters(); for ( int i = 0; i < numKeys; i++ ) { deleteStatement.setObject(i + 1, ((PrimaryKey) primaryKeys.get(i)).getValue()); } deleteStatement.executeUpdate(); --- 556,576 ---- for ( int i = 0; i < numKeys; i++ ) { deleteSQL.append( ((PrimaryKey) primaryKeys.get(i)).name ).append( " = ? " ); if ( i < numKeys - 1 ) { deleteSQL.append( " and " ); } } ! deleteStatement = mkStatement (deleteSQL.toString ()); } deleteStatement.clearParameters(); for ( int i = 0; i < numKeys; i++ ) { deleteStatement.setObject(i + 1, ((PrimaryKey) primaryKeys.get(i)).getValue()); } deleteStatement.executeUpdate(); *************** *** 609,629 **** paramSQL.append("?,"); } else { paramSQL.append("?)"); } } insertSQL.append(paramSQL.toString()); ! insertStatement = ((java.sql.Connection) connection).prepareStatement(insertSQL.toString()); Enumeration keys = updateValues.keys(); for ( int i = 1; keys.hasMoreElements(); i++) { String key = (String) keys.nextElement(); Object o = updateValues.get(key); if (o instanceof NullObject) insertStatement.setNull(i,java.sql.Types.NULL); else --- 615,635 ---- paramSQL.append("?,"); } else { paramSQL.append("?)"); } } insertSQL.append(paramSQL.toString()); ! insertStatement = mkStatement (insertSQL.toString ()); Enumeration keys = updateValues.keys(); for ( int i = 1; keys.hasMoreElements(); i++) { String key = (String) keys.nextElement(); Object o = updateValues.get(key); if (o instanceof NullObject) insertStatement.setNull(i,java.sql.Types.NULL); else *************** *** 945,966 **** PrimaryKey primaryKey = ((PrimaryKey) primaryKeys.get(i)); selectSQL.append(primaryKey.name).append("= ?"); if ( i < numKeys - 1 ) { selectSQL.append(" and "); } } if ( Driver.logDebug ) Driver.debug("selecting " + selectSQL.toString()); ! selectStatement = ((java.sql.Connection) connection).prepareStatement(selectSQL.toString()); ! for ( int j = 0, i = 1; j < numKeys; j++, i++) { selectStatement.setObject( i, ((PrimaryKey) primaryKeys.get(j)).getValue() ); } AbstractJdbc2ResultSet rs = (AbstractJdbc2ResultSet) selectStatement.executeQuery(); if ( rs.first() ) { --- 951,971 ---- PrimaryKey primaryKey = ((PrimaryKey) primaryKeys.get(i)); selectSQL.append(primaryKey.name).append("= ?"); if ( i < numKeys - 1 ) { selectSQL.append(" and "); } } if ( Driver.logDebug ) Driver.debug("selecting " + selectSQL.toString()); ! selectStatement = mkStatement (selectSQL.toString ()); for ( int j = 0, i = 1; j < numKeys; j++, i++) { selectStatement.setObject( i, ((PrimaryKey) primaryKeys.get(j)).getValue() ); } AbstractJdbc2ResultSet rs = (AbstractJdbc2ResultSet) selectStatement.executeQuery(); if ( rs.first() ) { *************** *** 1028,1048 **** PrimaryKey primaryKey = ((PrimaryKey) primaryKeys.get(i)); updateSQL.append(primaryKey.name).append("= ?"); if ( i < numKeys - 1 ) { updateSQL.append(" and "); } } if ( Driver.logDebug ) Driver.debug("updating " + updateSQL.toString()); ! updateStatement = ((java.sql.Connection) connection).prepareStatement(updateSQL.toString()); int i = 0; Iterator iterator = updateValues.values().iterator(); for (; iterator.hasNext(); i++) { Object o = iterator.next(); if (o instanceof NullObject) updateStatement.setNull(i+1,java.sql.Types.NULL); else updateStatement.setObject( i + 1, o ); --- 1033,1053 ---- PrimaryKey primaryKey = ((PrimaryKey) primaryKeys.get(i)); updateSQL.append(primaryKey.name).append("= ?"); if ( i < numKeys - 1 ) { updateSQL.append(" and "); } } if ( Driver.logDebug ) Driver.debug("updating " + updateSQL.toString()); ! updateStatement = mkStatement (updateSQL.toString ()); int i = 0; Iterator iterator = updateValues.values().iterator(); for (; iterator.hasNext(); i++) { Object o = iterator.next(); if (o instanceof NullObject) updateStatement.setNull(i+1,java.sql.Types.NULL); else updateStatement.setObject( i + 1, o );
В списке pgsql-patches по дате отправления: