Port Bug Report: Unable to insert large strings into a text datatype. - Mailing list pgsql-ports

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Steve Dyrdahl
Your email address      : dyrdahl@reticular.com

Category                : runtime: front-end: Java
Severity                : non-critical

Summary: Unable to insert large strings into a text datatype.

System Configuration
--------------------
  Operating System   : Solaris

  PostgreSQL version : 6.5

  Compiler used      :

Hardware:
---------
Sparc Ultra 10

Versions of other tools:
------------------------
Fetched a PostgreSQL ver6.5 binary for Solaris 7 from
ftp://postgresql.nextpath.com/pub/postgresql/bindist/6.5/
JDBC driver for Postgresql 6.5 JDK1.1.x downloaded from:
http://www.retep.org.uk/postgres/

--------------------------------------------------------------------------

Problem Description:
--------------------
I was attempting to insert large strings into a text
datatype field using Prepared Statements and JDBC. A summary
of what happens based on the size of the text string:

IF size <= 8132
THEN No error occurs.  The tuple is inserted correctly.

IF 8133 <= size <= 8151
THEN The following Exception is thrown:
java.sql.SQLException: ERROR:  Tuple is too big: size 8200
(Note the size value is dependent upon the size of the supplied string.)

IF size >= 8152
THEN The following exception gets thrown:
java.lang.IllegalArgumentException: Argument # > Arg length


Obviously getting the SQL Exception telling the user that
the supplied field is too large is not really a problem.
However, the IllegalArguementException is not very intuitive
for determining the problem.


--------------------------------------------------------------------------

Test Case:
----------
// This problem can be repeated by executing the following
// java program.  You will need to include the jdbc driver
// in your classpath, set the databasename in the url
// variable, and the username variable.
// In addition, it is possible the the hard-coded sizes
// may need to be changed depending upon the system?
import java.sql.*;
import java.util.*;

public class Bug
{
   private static final String url      = "jdbc:postgresql:databasename";
   private static final String username = "username";
   private static final String password = "";

   public static void main( String[] args )
   {
      // Register the jdbc driver.
      try
      {
         Class.forName("postgresql.Driver");
      }
      catch( Exception e )
      {
         System.out.println( "Unable to load jdbc driver." );
         return;
      }
      // Create a connection to the database.
      Connection con = null;
      try
      {
         con = DriverManager.getConnection( url, username, password );
      }
      catch( Exception e )
      {
         System.out.println( "Unable to create a connection to the database." );
         return;
      }

      // Create a temporary table.
      try
      {
         String create =
            "CREATE TABLE t (id int8 CONSTRAINT pk_t PRIMARY KEY, value text)";
         Statement stmt = con.createStatement();
         stmt.executeUpdate( create );
         stmt.close();
      }
      catch( Exception e )
      {
         System.out.println( e.toString() );
         System.out.println( "Unable to create table." );
         return;
      }
      // Build a string with a length of 8125.
      StringBuffer value = new StringBuffer();
      for( int i = 0; i < 8125; i++ )
      {
         value.append( "X" );
      }
      // Construct the prepared statement.
      PreparedStatement ps = null;
      try
      {
         String insert = "INSERT INTO t (id, value) VALUES(?, ?)";
         ps = con.prepareStatement( insert );
      }
      catch( Exception e )
      {
         System.out.println( e.toString() );
         System.out.println( "Unable prepare statement." );
      }
      // Attempt to insert strings with a length of 8125 to 8175.
      for( int j = 0; j < 50; j++ )
      {
         boolean ok = true;
         // Set the parameters.
         try
         {
            ps.setInt(    1, j );
            ps.setString( 2, value.toString() );
         }
         catch( Exception e )
         {
            System.out.println( e.toString() );
            System.out.println( "SET VALUES FAILED => " + value.length() );
            ok = false;
         }
         // If the parameter setting was successful, insert the values.
         if( ok )
         {
            try
            {
               ps.executeUpdate();
               System.out.println( "INSERT SUCCESS => " + value.length() );
            }
            catch( Exception e )
            {
               System.out.println( e.toString() );
               System.out.println( "INSERT FAILED  => " + value.length() );
            }
         }
         value.append( "X" );
      }
      // Drop the temporary table.
      try
      {
         ps.close();
         String    drop = "DROP TABLE t";
         Statement stmt = con.createStatement();
         stmt.executeUpdate( drop );
         stmt.close();
      }
      catch( Exception e )
      {
         System.out.println( e.toString() );
         System.out.println( "Unable to drop table." );
      }
   }
}


--------------------------------------------------------------------------

Solution:
---------


--------------------------------------------------------------------------