[
https://issues.apache.org/jira/browse/DERBY-5493?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-5493:
---------------------------------
Attachment: derby-5493-01-aa-correctnessPlusPeekerPlusTest.diff
Attaching derby-5493-01-aa-correctnessPlusPeekerPlusTest.diff. This patch
modifies how we allocate new sequence values, in order to fix the known
correctness problems with sequence generation. Regression tests pass cleanly
for me, but this patch is not ready for commit. It needs additional tests to
verify correctness, upgrade, and new user-visible features.
Mike and I discussed the correctness problems on DERBY-5443. Two proposals were
put forward, each of which had its own messy issues:
1) Use an invisible conglomerate and dedicated transaction to allocate new
sequence ranges. This is the approach taken by this patch.
2) Restrict the isolation level used to read from SYSSEQUENCES.
In that discussion, two problems with approach (1) were identified:
i) It creates a new file (the invisible conglomerate). I think that the space
occupied by this new file is very small compared to the size of an empty Derby
database and well within the growth we have tolerated for Derby feature
releases over the last 7 years.
ii) Orphaned tuples can pile up in the invisible conglomerate after successful
DROP SEQUENCE and unsuccessful CREATE SEQUENCE statements. I addressed this
problem by garbage-collecting the orphans at database boot time.
In addition to fixing the known correctness problem, this patch introduces the
following user-visible changes:
A) A new system function has been added: syscs_peek_at_sequence(). This
function gives the application the instantaneous current value of the sequence.
In previous releases, users tried to get this information by querying
SYSSEQUENCES.CURRENTVALUE. But that didn't work because that column holds the
end of the pre-allocation range and not the actual next value in the sequence.
B) SYSCONGLOMERATES.TABLEID is now nullable.
C) A new SYSGHOST conglomerate is listed in SYSCONGLOMERATES. The SYSGHOST
conglomerate does not belong to any corresponding table. Although users can't
see it, this is the shape of a SYSGHOST tuple:
( keycol varchar( 32672 ), payload Formatable )
In addition, this patch introduces a testing/diagnostic feature which we should
not document:
D) A new GhostTable VTI has been added. This lets you view the contents of
SYSGHOST. The VTI does all of its work in the transaction controller that is
dedicated to managing SYSGHOST. Here's how you invoke it:
select * from new org.apache.derby.diag.GhostTable() vti;
Behind the scenes, this patch introduces some other new objects:
E) GhostController, a synchronized object for reading/writing SYSGHOST tuples.
F) A new Formatable to hold the end of a pre-allocation range: SequenceState.
G) A new sequence updater for use on databases at level 10.9 or higher:
SyssequenceUpdater_10_9.
Most of the complexity of the patch is in the implementation of
GhostController. Extra support code was added to DataDictionaryImpl and
SyssequenceUpdater_10_9, but I tried to isolate most of the trickiness in
GhostControllerImpl.
This patch will require some changes to the Reference Manual:
DOC-1) Add a section describing the new syscs_peek_at_sequence() function.
DOC-2) Modify the section on SYSCONGLOMERATES to state that TABLEID is nullable.
DOC-3) Modify the section on SYSSEQUENCES to state that users should not bother
querying the CURRENTVALUE column. Instead, they should use
syscs_peek_at_sequence() to peek at the instantaneous current value of a
sequence generator.
This patch will require a release note explaining that users should use
syscs_peek_at_sequence() rather than SYSSEQUENCES.CURRENTVALUE.
Touches the following files:
--------------
M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
A java/engine/org/apache/derby/iapi/sql/dictionary/SequenceState.java
New Formatable to hold the end of pre-allocation ranges.
--------------
M
java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java
A java/engine/org/apache/derby/iapi/sql/dictionary/GhostDescriptor.java
New tuple describing a row in SYSGHOST.
--------------
M java/engine/org/apache/derby/iapi/sql/dictionary/CatalogRowFactory.java
M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
M
java/engine/org/apache/derby/impl/sql/catalog/SYSCONGLOMERATESRowFactory.java
M java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
Support for creating SYSGHOST and deleting orphans.
--------------
M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
A java/engine/org/apache/derby/iapi/sql/dictionary/GhostController.java
A java/engine/org/apache/derby/impl/sql/catalog/GhostControllerImpl.java
Logic to manage SYSGHOST.
--------------
M java/engine/org/apache/derby/catalog/SystemProcedures.java
Logic for new syscs_peek_at_sequence() procedure.
--------------
A java/engine/org/apache/derby/diag/GhostTable.java
M tools/jar/extraDBMSclasses.properties
New diagnostic VTI for viewing SYSGHOST.
--------------
M java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java
New sequence updater for use on databases at level 10.9 and higher.
--------------
M
java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java
Add a corresponding SYSGHOST tuple when creating a sequence. If the create
action is rolled back, then the SYSGHOST tuple will be garbage-collected the
next time the database boots.
--------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java
Slight change to use GhostTable rather than SYSSEQUENCES.CURRENTVALUE in order
to view the end of pre-allocation ranges.
--------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.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/tests/upgradeTests/Changes10_2.java
M java/testing/org/apache/derbyTesting/functionTests/master/ij7.out
Test changes to account for the metadata changes.
> Same value returned by successive calls to a sequence generator.
> ----------------------------------------------------------------
>
> Key: DERBY-5493
> URL: https://issues.apache.org/jira/browse/DERBY-5493
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2,
> 10.9.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Labels: derby_triage10_9
> Attachments: derby-5493-01-aa-correctnessPlusPeekerPlusTest.diff
>
>
> The following script shows the same value being returned from a sequence
> generator by two successive NEXT VALUE FOR calls. Thanks to Knut for finding
> this:
> connect 'jdbc:derby:memory:db;create=true';
> create table t (x int);
> create sequence s;
> autocommit off;
> select count(*) from sys.syssequences with rs;
> values next value for s;
> drop table t;
> rollback;
> -- same value as previous call
> values next value for s;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
