db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (Updated) (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-5493) Same value returned by successive calls to a sequence generator.
Date Tue, 27 Mar 2012 14:46:25 GMT

     [ 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

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


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

  ( 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()

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:,,,,,
>            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


View raw message