Forum Stats

  • 3,838,105 Users
  • 2,262,332 Discussions
  • 7,900,506 Comments

Discussions

Autoincrement of primary key

2»

Comments

  • dvohra21
    dvohra21 Member Posts: 14,618 Gold Crown

    Well, if you are going to "generate always" then you can't use some other arbitrary value, so the error message makes sense. But back to your proposal for a new feature...

    Well, if you are going to "generate always" then you can't use some other arbitrary value

    The proposal is for an auto_increment similar to MySQL with which a different value than the value that would be generated with auto increment may be added with INSERT statement. And "When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value."

  • dvohra21
    dvohra21 Member Posts: 14,618 Gold Crown

    There are many options how to use sequences in the correct way. The "autoincrement" feature is there since 12c.

    You never explained what you understand by it.

    For all objections raised so far, there are solutions, even if you don't know them.

    So I don't see a need to add any other option that does (almost?) the same as what is possible already

    For example when you call the feature AUTOincrement. Why would you allow MANUAL setting of the surrogate key?
    Btw. this is possible useing a very similar feature that was also introduced in 12c. We can set a default value for the column and say this default kicks in, if the value provided is null: "DEFAULT mySeq.nextval ON NULL"  . Which also means, we can set other values, where the default will not overwrite our value. However then it is not generated always.

    The main problem here seems to be, that you want to use some "magic values" like 999. Which is generally considered bad programming practice. Maybe I misunderstand your intention, but this is what your example does look like. If you have an unknown value, why not use NULL at this point in time.

    https://en.wikipedia.org/wiki/Magic_number_%28programming%29

    The term magic number or magic constant refers to the anti-pattern of using numbers directly in source code. This has been referred to as breaking one of the oldest rules of programming, dating back to the COBOL, FORTRAN and PL/1 manuals of the 1960s.
    [1]

    Auto increment is only if a user does not specify a value. The proposal is for an auto_increment similar to MySQL with which a different value than the value that would be generated with auto increment may be added.

    And the sequence may be reset :  "When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value."

  • Sven W.
    Sven W. Member Posts: 10,537 Gold Crown

    Auto increment is only if a user does not specify a value. The proposal is for an auto_increment similar to MySQL with which a different value than the value that would be generated with auto increment may be added.

    And the sequence may be reset :  "When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value."

    dvohra21 wrote:Auto increment is only if a user does not specify a value. The proposal is for an auto_increment similar to MySQL with which a different value than the value that would be generated with auto increment may be added. And the sequence may be reset : "When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value."

    Good now you gave a definition. I think it is a definition that is not well suited for a performant multi user system.

    Currently this is possible, although difficult to code. The reset option requires additional work and introduces major serialization issues.

    Can you give a reason for such a strange requirement? I mentioned before, you want to put meaning into meaningless technical keys. This is bound to create problems.

    Imagine the following situation: The table has the value 1-50 already.

    Two different sessions (=users) set the value at almost the same. Session a chooses 60, session b chooses 70.

    Now what should happen when

    1) in this order: A inserts, A commits, B inserts, B commits, A inserts,

    I assume the second insert of A would then be 71, although this would come as a minor surprise to A. But at least it can see that there is another value 70 inserted by another session.

    2) in this order: A inserts, A commits, B inserts, B rollbacks, A inserts

    One could think the second A insert should then be 61.

    However this means that the result for the second insert depends on and has to wait for the result of the transaction in session B. If B doesn't commit or rollback, A can not decide what the autoincrement value would be.

    The better way is that each insert triggers the logic in an autonomous way, then the value would be 71. This would be a major surprise for session A. But it is very much a like what current sequences do, although they have no need for setting the value manually.

    Performance suffers either because one session has to wait for the other (very bad), or because the insert needs to persist the new value that is stored across sessions (not so bad. similar to using NOCACHE currently).

    btw. currently you could alter the options of the sequence behind an identity column sequence by using an alter table command.

    But that works only well if no other session inserts into the table at the same time.

    Here is a crude example:

    /* prepare demo table */create table testauto (id number generated always as identity primary key                      ,name varchar2(500)                      );                      /* add some data */insert into testauto (name) values ('test1.');insert into testauto (name) values ('test2.');commit;select * from testauto;/* check data dictionary and find the sequence name*/select * from user_sequences; -- in my case the seqeunce name is ISEQ$$_172100/* reset the cache so that we can see the next value in the dictionary */select cache_size from user_sequences where sequence_name = 'ISEQ$$_172100';alter table testauto modify id generated always as identity nocache; /*  change the increment so that it jumps over all the other values. */select last_number from user_sequences where sequence_name = 'ISEQ$$_172100';alter table testauto modify id generated always as identity increment by 57 nocache; -- 57 = 60 (target value)-3(last number)insert into testauto (name) values ('dummy. rollback immediatly.'); -- this does a sequence.nextval which we can not call otherwiserollback;/*  reset the changes */alter table testauto modify id generated always as identity increment by 1 cache 20;/*  check the result in the dictionary*/select last_number, cache_size from user_sequences where sequence_name = 'ISEQ$$_172100';/*  test the result using the the table*/insert into testauto (name) values ('test3.');select * from testauto;commit;/* undo testcase */drop table testauto purge;
    William Robertson
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    Well, if you are going to "generate always" then you can't use some other arbitrary value

    The proposal is for an auto_increment similar to MySQL with which a different value than the value that would be generated with auto increment may be added with INSERT statement. And "When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value."

    dvohra21 wrote:The proposal is for an auto_increment similar to MySQL with which a different value than the value that would be generated with auto increment may be added with INSERT statement. And "When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value."

    How are we supposed to know all of that?

    So if it currently has generated values 1-100 and you explicitly specify value 1000, the next generated value will be 1001? And if you specify 10 then what?

    Sounds like a terrible idea, sorry.

    JiF Brodeur
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,833 Red Diamond

    Well, if you are going to "generate always" then you can't use some other arbitrary value

    The proposal is for an auto_increment similar to MySQL with which a different value than the value that would be generated with auto increment may be added with INSERT statement. And "When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value."

    dvohra21 wrote:And "When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value."

    This requires serialisation of the multi-user, multi-session, database.

    SERIALISATION DESTROYS PERFORMANCE.

    So it is a bloody no from my side.