db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5151) Sequence without cache
Date Fri, 25 Mar 2011 15:22:05 GMT

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

Rick Hillegas commented on DERBY-5151:

Thanks Bryan and Mark for continuing the discussion. Here are pointers to the syntax used
by some other databases:

Oracle: http://www.dba-oracle.com/t_oracle_create_sequence.htm

DB2: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004201.htm

Postgres: http://www.postgresql.org/docs/8.1/static/sql-createsequence.html

>From these links, it appears to me that Oracle and DB2 support identical syntax:

[ CACHE cacheSize | NOCACHE ]    // the default is CACHE 20

Postgres supports slightly different syntax:

[ CACHE cacheSize ]  // the default is CACHE 1, which means caching is turned off

I agree with Mark and Bryan that adding more property-based knobs is awkward. I share Bryan's
concerns that the proposed SQL syntax has not made it into the standard yet. However, I am
encouraged by the agreement between Oracle and DB2 and by the overlap with Postgres. The Postgres
syntax would be compatible with all 3 databases.

I would be comfortable with the Postgres syntax, preserving the current Derby default of cacheSize=5.
I would also be comfortable with the extra NOCACHE syntactic sugar supported by Oracle and
DB2. Other opinions?


> 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

View raw message