db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6852) Allow identity columns to cycle (as defined in SQL:2003)
Date Tue, 09 Aug 2016 23:57:20 GMT

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

Bryan Pendleton commented on DERBY-6852:
----------------------------------------

The new _4.diff patch looks very good to me. Thank you for adding
all those tests!

I wonder if you could add a few more tests, to explore the behavior
of the ALTER TABLE statement on generated columns with cycle defined.

For example:

1) if column 'a' is 'generated always as identity (start with 2147483647 cycle)', and you
issue:

    alter table t alter column a set increment by 4

   and then you insert some rows into the table, does it still correctly cycle?

2) if column 'a' is 'generated always as identity (increment by 2 cycle)', and you issue:

    alter table t alter column a restart with 2147483647 

   and then you insert some rows into the table, does it still correctly cycle?

I wonder, do you think we should file a separate JIRA so that, someday,
it would be possible to use ALTER TABLE to set the CYCLE option on or off
for an existing GENERATED AS IDENTITY column?

Also, I wonder if we need to file a separate JIRA to see whether, someday,
we should extend SYS.SYSCOLUMNS so that it can report the CYCLE option's
value for a GENERATED AS IDENTITY column?

Can you add some tests that include SELECT from SYS.SYSCOLUMNS for a
generated column which has the CYCLE option, just to verify that, with
the current code, there is no harmful behavior (crash, etc.)? 

Can can you also add some tests that include SELECT from SYS.SYSSEQUENCES
after defining some generated columns with CYCLE option, to verify that
the CYCLE option is reported properly for the underlying system sequence
that is used for the generated column?

All your code changes look very clean to me. It seems like, if we don't
expose any new problems with these few additional tests (and any other
tests that you're still working on), we should be pretty close to ready
with this patch -- what do you think?

bryan


> Allow identity columns to cycle (as defined in SQL:2003)
> --------------------------------------------------------
>
>                 Key: DERBY-6852
>                 URL: https://issues.apache.org/jira/browse/DERBY-6852
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Peter Hansson
>            Assignee: Danoja Dias
>         Attachments: derby-6852_1.diff, derby6852doc.diff, derby_6852_2.diff, derby_6852_3.diff,
derby_6852_4.diff, script.sql
>
>
> Currently when an IDENTITY column reaches its maximum value it will produce an error.
> For tables that are used as 'transaction logs' or 'event logs' it often makes sense to
let the table automatically start over with the first identity value again when the max is
reached. This would be similar to the CYCLE option on Oracle's SEQUENCE and as defined in
SQL:2003. And Derby is probably used quite often for this purpose, I guess, perhaps even more
than other RDBMSs.
> At the moment every developer have to program their own logic for this.
> I propose to introduce the CYCLE option.
> The idea of CYCLE is based on the assumption that there's been a prior cleanup in the
table rows so that it will be possible to re-use ids that have been used previously. If that
is not the case - and a rollover happens - then a duplicate value error will occur. In this
sense it can be argued that the CYCLE option will trade a _certain_ error for a _potential_
error. Most Derby users would possibly gladly accept such a bargain. In other words: This
option will greatly enhance the usability of IDENTITY columns.
> The current implementation of IDENTITY columns SQL grammar in Derby is a subset of the
SQL:2003 standard which is the first of the SQL standards to define IDENTITY columns. Interestingly
the standard also defines the CYCLE option but this was never implemented in Derby. Also see
[SQL-99 and SQL-2003 features mapped to Derby|https://wiki.apache.org/db-derby/SQLvsDerbyFeatures]
(scroll to T174).
> In other words: The proposal is simply to implement CYCLE as defined in SQL:2003.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message