[
https://issues.apache.org/jira/browse/DERBY-6542?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6542:
---------------------------------
Attachment: derby-6542-01-ab-catalog.diff
Attaching derby-6542-01-ab-catalog.diff. This patch adds catalog support for
backing identity columns with system-generated sequences. I am running tests
now.
This patch does the following:
1) Creates a system-generated sequence when a table is created with an identity
column and the database is at level 10.11 or higher. Note that you can't
currently add an identity column to an existing table.
2) Drop the system-generated sequence when the identity column is dropped.
3) Drop the system-generated sequence when the table is dropped.
4) Add a system-generated sequence for every identity column when
hard-upgrading to 10.11.
5) Add a new system function, SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY, when creating
a 10.11 database or when hard-upgrading to 10.11.
Touches the following files:
-----------------
M java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
Add a method for assigning a unique name to the system-generated sequence.
-----------------
M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
M java/engine/org/apache/derby/catalog/SystemProcedures.jav
M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
M java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
Support for SYSCS_PEEK_AT_IDENTITY. Upgrade support.
-----------------
M java/tools/org/apache/derby/impl/tools/dblook/DB_Sequence.java
dblook shouldn't create DDL for the system-generated sequences.
-----------------
M java/engine/org/apache/derby/iapi/types/TypeId.java
M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
Add support for computing the min/max bounds of an integer type.
-----------------
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
Don't let users directly issue a NEXT VALUE FOR on a system-generated sequence.
-----------------
M
java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java
Make a system-generated sequence for a new identity column.
-----------------
M
java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java
Drop the system-generated sequence when the table is dropped.
-----------------
M
java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Drop and recreate the system-generated sequence when the identity column is
modified. Drop the system generated sequence when the identity column is
dropped.
-----------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/TestDbMetaData.java
M java/testing/org/apache/derbyTesting/functionTests/master/ij7.out
Adjust existing tests as necessary.
-----------------
A
java/testing/org/apache/derbyTesting/functionTests/tests/lang/IdentitySequenceTest.java
Basic test for catalog support of system-generated sequences.
-----------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java
Basic upgrade tests for system-generated sequences.
> Improve the concurrency of identity columns by using SYS.SYSSEQUENCES
> ---------------------------------------------------------------------
>
> Key: DERBY-6542
> URL: https://issues.apache.org/jira/browse/DERBY-6542
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-6542-01-ab-catalog.diff
>
>
> This is another attempt to improve the concurrency of identity columns. The
> previous attempt was tracked by DERBY-4437.
> This new attempt will try out Mike's last suggestion: use system-created
> sequences managed by SYS.SYSSEQUENCES. This should reduce the contention on
> the core catalogs.
> I'm hopeful about this approach because of the experiments tracked by
> DERBY-6533. There we are not seeing any problems related to sequence
> generators but we are seeing lots of identity-related lock timeouts.
> Here is the general shape of this approach:
> 1) When adding an identity column to a table, Derby will create a sequence
> generator for the column. The sequence generator will live in the SYS schema
> and its name will be the table's UUID.
> 2) DROP SEQUENCE will not operate on system-created sequences. System-created
> sequences will be dropped/modified by DROP/ALTER TABLE commands.
> 3) We will add a new system function for inspecting the current, in-memory
> value of an identity generator without getting a lock on SYS.SYSSEQUENCES:
> SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY( tableSchemaName, tableName ).
> 4) Derby will create a sequence for every legacy identity column after
> hard-upgrade.
> 5) These changes will take place only after hard-upgrade. Soft-upgrade will
> not change the behavior of identity columns.
> Comments on this proposal are welcome. Thanks.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
