db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r886162 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/TableElementList.java testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
Date Wed, 02 Dec 2009 14:49:19 GMT
Author: bpendleton
Date: Wed Dec  2 14:49:19 2009
New Revision: 886162

URL: http://svn.apache.org/viewvc?rev=886162&view=rev
Log:
DERBY-3947: Cannot insert 994 char string into indexed column

A table created with "CREATE TABLE t (x varchar(1000) primary key)" could
encounter problems when a particularly long value of "x" was inserted,
because the index that was automatically created to support the PRIMARY KEY
constraint was created with a small page size. Such an insert statement
would get an error like: "Limitation: Record of a btree secondary index
cannot be updated or inserted due to lack of space on the page."

This change enhances TableElementList so that, when creating an index for
a constraint, it now checks the approximate length of the columns in the
index, and, if they are sufficiently long, automatically chooses a larger
default page size for the index conglomerate.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java?rev=886162&r1=886161&r2=886162&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
Wed Dec  2 14:49:19 2009
@@ -25,6 +25,8 @@
 import org.apache.derby.iapi.services.sanity.SanityManager;
 
 import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.reference.Property;
+import org.apache.derby.iapi.services.property.PropertyUtil;
 
 import org.apache.derby.iapi.sql.StatementType;
 import org.apache.derby.iapi.sql.compile.CompilerContext;
@@ -61,6 +63,7 @@
 import java.util.ArrayList;
 import java.util.Hashtable;
 import java.util.HashSet;
+import java.util.Properties;
 import java.util.Vector;
 
 /**
@@ -1298,9 +1301,64 @@
                     isAscending,
                     isConstraint,
                     cdn.getBackingIndexUUID(),
-                    cdn.getProperties());
+                    checkIndexPageSizeProperty(cdn));
 		}
 	}
+    /**
+     * Checks if the index should use a larger page size.
+     *
+     * If the columns in the index are large, and if the user hasn't already
+     * specified a page size to use, then we may need to default to the
+     * large page size in order to get an index with sufficiently large pages.
+     * For example, this DDL should use a larger page size for the index
+     * that backs the PRIMARY KEY constraint:
+     *
+     * create table t (x varchar(1000) primary key)
+     *
+     * @param cdn Constraint node
+     *
+     * @return properties to use for creating the index
+     */
+    private Properties checkIndexPageSizeProperty(ConstraintDefinitionNode cdn) 
+        throws StandardException
+    {
+        Properties result = cdn.getProperties();
+        if (result == null)
+            result = new Properties();
+        if ( result.get(Property.PAGE_SIZE_PARAMETER) != null ||
+             PropertyUtil.getServiceProperty(
+                 getLanguageConnectionContext().getTransactionCompile(),
+                 Property.PAGE_SIZE_PARAMETER) != null)
+        {
+            // do not override the user's choice of page size, whether it
+            // is set for the whole database or just set on this statement.
+            return result;
+        }
+        ResultColumnList rcl = cdn.getColumnList();
+        int approxLength = 0;
+        for (int index = 0; index < rcl.size(); index++)
+        {
+            String colName = ((ResultColumn) rcl.elementAt(index)).getName();
+            DataTypeDescriptor dtd;
+            if (td == null)
+                dtd = getColumnDataTypeDescriptor(colName);
+            else
+                dtd = getColumnDataTypeDescriptor(colName, td);
+            // There may be no DTD if the column does not exist. That syntax
+            // error is not caught til later in processing, so here we just
+            // skip the length checking if the column doesn't exist.
+            if (dtd != null)
+                approxLength+=dtd.getTypeId().getApproximateLengthInBytes(dtd);
+        }
+        if (approxLength > Property.IDX_PAGE_SIZE_BUMP_THRESHOLD)
+        {
+            result.put(
+                    Property.PAGE_SIZE_PARAMETER,
+                    Property.PAGE_SIZE_DEFAULT_LONG);
+        }
+        return result;
+    }
+
 
 	/**
 	 * Check to make sure that there are no duplicate column names

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java?rev=886162&r1=886161&r2=886162&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
Wed Dec  2 14:49:19 2009
@@ -904,4 +904,56 @@
         st1.close();
         st.close();
     }
+    // This test verifies that if the PRIMARY KEY constraint mentions a
+    // column which is potentially large, then Derby will automatically
+    // choose a large pagesize for the index's conglomerate (DERBY-3947)
+    //
+    public void testPrimaryKeyPageSizeDerby3947()
+        throws SQLException
+    {
+        st = createStatement();
+        st.executeUpdate("create table d3947 (x varchar(1000) primary key)");
+        char[] chars = new char[994];
+        PreparedStatement ps = prepareStatement("insert into d3947 values (?)");
+        ps.setString(1, new String(chars));
+        ps.executeUpdate();
+        ps.close();
+        checkLargePageSize(st, "D3947");
+        st.executeUpdate("drop table d3947");
+
+        // A second variation is to add the PK constraint using ALTER TABLE;
+        // A third variation is to add a FK constraint
+        st.executeUpdate("create table d3947 (x varchar(1000) not null, " +
+                " y varchar(1000))");
+        st.executeUpdate("alter table d3947 add constraint " +
+                "constraint1 primary key (x)");
+        st.executeUpdate("alter table d3947 add constraint " +
+                "constraint2 foreign key (y) references d3947(x)");
+        checkLargePageSize(st, "D3947");
+        // Ensure we still get the right error message when col doesn't exist:
+        assertStatementError("42X14", st,
+                "alter table d3947 add constraint " +
+                "constraint3 foreign key (z) references d3947(x)");
+        st.executeUpdate("drop table d3947");
+
+        st.close();
+    }
+    private void checkLargePageSize(Statement st, String tblName)
+        throws SQLException
+    {
+        ResultSet rs = st.executeQuery(
+            "select * from TABLE(SYSCS_DIAG.SPACE_TABLE('"+tblName+"')) T");
+        while (rs.next())
+        {
+            if ("1".equals(rs.getString("isindex")))
+                assertEquals(32768, rs.getInt("pagesize"));
+            else
+                assertEquals(4096, rs.getInt("pagesize"));
+
+            //System.out.println(rs.getString("conglomeratename") +
+            //        ","+rs.getString("isindex")+
+            //        ","+rs.getString("pagesize"));
+        }
+        rs.close();
+    }
 }



Mime
View raw message