You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2009/12/02 15:49:19 UTC
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
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();
+ }
}