Single sequence for every table

[wiesiek@…]

Hi,
I am a newbie in Django,
but IMHO in database layer design Django does not follow DRY principle.
I do not understand, why Django uses different sequence for every table.
Having single sequence for all tables is much better than having
sequences for different tables regarding
maintenance, sharding, security et.c. I give you and example package,
that can greatly simplify many mentioned tasks.
Just use django_seq as id generator on any django table

CREATE OR REPLACE PACKAGE django_seq IS

  FUNCTION NEXTVAL RETURN NUMBER;
  FUNCTION CURRVAL RETURN NUMBER;
END django_seq;
/
CREATE OR REPLACE PACKAGE BODY django_seq IS

  last_val NUMBER;
  shard_no NUMBER;

  FUNCTION NEXTVAL RETURN NUMBER IS
  BEGIN
    SELECT to_number(to_char(mr_inne_seq.nextval) || lpad(shard_no, 4, '0')) ||
           ltrim(to_char(MOD(abs(hsecs), 1000000), '000000'))
      INTO last_val
      FROM sys.v_$timer;
    RETURN last_val;
  END;

  FUNCTION CURRVAL RETURN NUMBER IS
    no_current_value EXCEPTION;
    PRAGMA EXCEPTION_INIT(no_current_value, -8002);
  BEGIN
    IF (last_val IS NULL) THEN
      RAISE no_current_value;
    END IF;
    RETURN last_val;
  END CURRVAL;

BEGIN
  --- the code below can use database table lookup
  shard_no := 5;

END django_seq;
/

[Aymeric Augustin]

Fixed formatting (please use preview).

As is, I can't tell where we'd use your block of code, what are its advantages and drawbacks, and whether it is backwards-compatible. We're web developers, not DBAs :) All I can say is that it is far from trivial.

As a consequence, this ticket is likely to fall into the "yes, Django could have been designed differently, but it isn't" bucket, but I'll leave it open for now in case someone's interested.

If you want to see this in Django, you will have to 1) produce a patch 2) explain why it is useful — your explanation is mostly hand-waving at this point.

[Aymeric Augustin]

Also, I believe #13295 would resolve this to some extend: it would allow you to use whatever sequence you want. So you could use the same sequence for all tables in your project if you wanted to.

[anonymous]

I forgot to mention, that this code works only for Oracle and
it needs larger Id column (e.g. Oracle number(38)). I am also not a DBA,
but I am a database application developer and beginning web developer.
I think that using many sequences in a database application is very common
design flaw and nuissance in allmost all database applications I met
so far :-)

[Jacob]

As aaugustin says, this was just a decision we made years ago. I don't see that there's a great advantage either way: each option has its pros and cons. I think #13295 is the answer here: let users decide if they feel strongly. Thus, I'm marking this closed as a duplicate of #13295.

Thanks!