[jira] [Commented] (DERBY-5151) Sequence without cache

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-5151?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13011184#comment-13011184 ] 

Rick Hillegas commented on DERBY-5151:
--------------------------------------

Hi Mark. This is an interesting problem. Here's something to keep in mind: there will always be gaps in the sequence because sequence-number-generating statements can be rolled back. They can be rolled back because of Derby bugs or statement level errors like constraint violations, or because the application discards the surrounding transaction for some reason. Once a sequence number has been allocated, the counter is advanced and never backs up. If the statement which allocated the number is rolled back, then there will be a hole in the sequence. Will your use-case tolerate these holes?

I'm not keen on adding more SQL language to address this problem. Fortunately however, the sequence generator was designed so that we could tune the cache size (SequenceGenerator.DEFAULT_PREALLOCATION_COUNT). I would prefer to tune the cache size via Derby properties:

o You could introduce a master Derby property which controls the size of all sequence generator caches, say derby.sequence.cache.size.

o You could introduce a space of per-sequence properties, say derby.sequence.cache.size.sequenceName.

Thanks,
-Rick



> Sequence without cache
> ----------------------
>
>                 Key: DERBY-5151
>                 URL: https://issues.apache.org/jira/browse/DERBY-5151
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Mark Holster
>         Attachments: sequence-no-cache.patch
>
>
> Currently, a sequence in Derby always uses a pre allocating cache. I'm working on an usecase which requires a number to increase with a fixed value. The usecase fails after a restart because of the pre allocating cache.
> I've created a patch that adds the options CACHE | NO CACHE to the create sequence statement. When no cache is provided, the pre allocating cache size will be set to 1, which results in a "no cache sequence".
> I've followed the CYCLE | NO CYCLE code as much as possible. Tested it in my own app and it seems to work fine.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira