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