Two patches - Mailing list 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 );