[
https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-712:
--------------------------------
Attachment: derby-712-05-af-sequenceGenerator.diff
Attaching derby-712-05-af-sequenceGenerator.diff. This patch adds cached
sequence generators to the data dictionary and makes the NEXT VALUE FOR clause
yield the expected results. Regression tests have passed for me but I am
re-running them just to be safe.
User-visible behavior changes as follows:
A) When you issue a NEXT VALUE FOR clause, the value of the sequence generator
really advances as expected.
B) If you shutdown the database after using a sequence generator, the generator
may leak up to 5 values. That is, when you reboot, the next value for the
generator may be up to 5 steps beyond what you expected.
Here is the approach I adopted:
1) SequenceGenerator - This is a general class which manages ranges of sequence
values. It advances a counter and hands out the next sequence value in a range.
In addition, it calculates chunk sizes for sub-ranges which can be
pre-allocated. Right now, pre-allocation is pretty dumb: we just pre-allocate 5
numbers at a time. In the future we could do something fancier. For instance,
we could give the user a property for tuning the size of pre-allocated ranges,
or Derby could auto-tune this number itself based on run-time behavior.
2) SequenceUpdater - This is an abstract class. It is a Cacheable to be stored
in the data dictionary. Concrete subclasses are responsible for particular
types of sequences. For instance, there is an inner subclass which is
responsible for ANSI/ISO sequence generators. In the future, we could add
another inner subclass which is responsible for identity columns. There is also
a testing subclass which I used to test boundary conditions related to the
currently unimplemented optional clauses of the CREATE SEQUENCE statement. The
SequenceUpdater does the following tasks:
a) It calls its corresponding SequenceGenerator to calculate the next value in
a sequence.
b) It manages the updating of the system tables when a pre-allocated range
needs to be recorded on disk.
c) It reclaims unused pre-allocated values when the data dictionary caches have
to be flushed. This prevents us from leaking values when DDL is performed. In
the future, similar reclamation could be performed during the orderly shutdown
of a database. This probably requires some discussion.
There are some tricky bits in the implementation:
i) There are a couple synchronized methods in SequenceGenerator. It is my hope
that these critical sections are short.
ii) The on-disk catalog value (in this case, SYS.SYSSEQUENCES.CURRENTVALUE) is
updated in a read/write subtransaction of the session's execution transaction
if possible--and that subtransaction is then immediately committed. However, if
it is not possible to obtain an immediate lock on the catalog row, we fall back
to updating the row in the parent transaction, that is, in the session's
execution transaction. This is similar to what is done for identity columns
today.
iii) Updating the value in the catalog row is managed by a little hopping back
and forth among the DataDictionary, the SequenceUpdater, and the
SequenceGenerator. Here's what the flow of control looks like:
o An Activation asks the DataDictionary for the next value in a sequence.
o The DataDictionary forwards the request to the SequenceUpdater.
o The SequenceUpdater forwards the request to the SequenceGenerator.
o If the SequenceGenerator determines that a new range needs to be allocated,
it calculates the new endpoint of the range. Then the SequenceGenerator asks
the SequenceUpdater to allocate the new range.
o The SequenceUpdater forwards that request to the DataDictionary.
o The DataDictionary updates the catalog row.
o Everyone returns and the Activation gets the value it asked for.
Touches the following files:
-------
A java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java
The SequenceGenerator described in (1) above.
-------
A java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java
The SequenceUpdater described in (2) above.
-------
M java/engine/org/apache/derby/iapi/reference/Property.java
M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
DataDictionary support for cached sequence generators.
-------
M java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java
M java/engine/org/apache/derby/impl/sql/catalog/SYSSEQUENCESRowFactory.java
M
java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java
M
java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java
A null in SYS.SYSSEQUENCES.CURRENTVALUE now means that a non-cycling sequence
generator is exhausted. That is, it has run through its range of legal values.
-------
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
New error message for exhausted sequence generators.
-------
M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
Code-generation for the NEXT VALUE FOR clause.
-------
M java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
Run-time support for the NEXT VALUE FOR clause.
-------
A
java/testing/org/apache/derbyTesting/functionTests/tests/lang/SGVetter.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
A
java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java
Regression tests for cached sequence generators. SGVetter is a
re-implementation of sequence generation used to test the correctness of the
production SequenceGenerator. SGVetter is less efficient than SequenceGenerator
but it is easier to reason about its correctness.
-------
> Support for sequences
> ---------------------
>
> Key: DERBY-712
> URL: https://issues.apache.org/jira/browse/DERBY-712
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Environment: feature request
> Reporter: Tony Dahbura
> Assignee: Suran Jayathilaka
> Fix For: 10.6.0.0
>
> Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch,
> catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch,
> catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff,
> create_drop_sequence_a.patch, create_drop_sequence_b.patch,
> create_drop_sequence_c.patch, create_drop_sequence_d.patch,
> create_sequence_a.patch, createseq_args_bind_a.diff,
> derby-712-02-aa-privilegeNodeCleanup.diff,
> derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff,
> derby-712-04-aa-dblook.diff, derby-712-05-af-sequenceGenerator.diff,
> SequenceGenerator.html, sequences_next_value_a.patch
>
>
> Would like to see support added for sequences. This would permit a select
> against the sequence to always obtain a ever increasing/decreasing value.
> The identity column works fine but there are times for applications where the
> application needs to obtain the sequence number and use it prior to the
> database write. Subsequent calls to the table/column would result in a new
> number on each call.
> SQL such as the following:
> SELECT NEXT VALUE FOR sequence_name FROM sometable ; would result in a next
> value.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
