Code

Opened 4 years ago

Last modified 3 years ago

#13295 new New feature

Add a Meta.sequence option to models

Reported by: smglab Owned by: nobody
Component: Database layer (models, ORM) Version: 1.1
Severity: Normal Keywords: sequence last_id postgresql
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: yes
Needs tests: yes Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by jacob)

PostgreSQL users need to be able to override the name of the serial sequence. Mostly to match existing databases, but also to solve issues like #15682, #13179 and #1946.

Attachments (2)

postgresql_name_seq.diff (1.1 KB) - added by smglab 4 years ago.
postgresql_name_seq.2.diff (2.5 KB) - added by smglab 4 years ago.

Download all attachments as: .zip

Change History (10)

Changed 4 years ago by smglab

Changed 4 years ago by smglab

comment:1 Changed 4 years ago by ramiro

  • Keywords sequence added; sequnce removed
  • milestone 1.2 deleted
  • Needs documentation set
  • Needs tests set

comment:2 Changed 4 years ago by russellm

  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Accepted

It's an edge case, but one that's probably worth supporting.

Regarding the patch --

  • it obviously needs tests.
  • It seems to ignore the creation of sequence names in the first place
  • It seems to be appending "_seq" to the name of the provided sequence name. Why? If I manually specify a sequence name, shouldn't it be used verbatim?
  • _meta.db_seq should always return the correctly constructed sequence name; we shoulnd't need to use 'if pk_name' or similiar tricks to work out if the sequence has been manually specified.

comment:3 Changed 4 years ago by akaariai

At least tickets #8901, #13179 and #1946 deal with the same problem. There are actually a lot more issues than just fixing last_insert_id to use the custom db_seq. Postgresql truncates sequence names to 63 characters, and we need to deal with table creation, sequence resetting and sql_flush. We still need to think how to deal with problems presented by users trying to use the same sequence for multiple tables. And we need to take care of automatically generated m2m tables and their sequences.

First problem is ticket #8901. If it happens so that the table_name + column_name + _seq is longer than 63 characters, then the sequence name will be truncated to 63 characters in a complex way by postgresql. Complex here means that it is not easy to create the right sequence name for all supported versions of postgresql. Django currently doesn't work at all for tables where the table_nam + column_name is longer than 63 characters.

There is a function pg_get_serial_sequence(tablename, column) (see ticket #13179), which returns the sequence name for given column. Unfortunately this only works for tables having serial columns, that is

    id serial not null primary key

, not for tables having column of type

    id integer primary key default nextval('some_custom_sequence_name')

This means that using pg_get_serial_sequence will result in backwards incompatible behaviour for custom schemas where the default nextval way of specifying the sequence is used, and it can't be used with custom sequence names. Issuing postgresql command

    ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

will fix the problem, but it is still a backwards incompatible change. It is impossible to have the sequence owned by multiple tables, so I don't think using pg_get_serial_sequence is a good solution.

The proposed solution:

Models get a new meta attribute, db_seq. In initialization we set up _meta.db_seq to always contain the right sequence name for the model, either the given db_seq or %s_%s_seq % (table_name, autofield_column_name). In model validation we check that the db_seq is shorter than 63 characters long, indifferent of if it is automatically generated or given as meta option. This should not be backwards incompatible, as currently if the sequence is longer than 63 characters django will not work.

In table generation, if db_seq is not automatically generated, we generate the pk column as "col_name integer not null primary key default next_val('db_seq')", else we will use the current behaviour of "col_name serial not null primary key". When using the next_val definition we need to generate the sequence before we generate the table. We need to also alter some other places (at least db/backends/postgresql/options.py functions sql_flush, last_insert_id, and sequence_reset_sql).

There is another problem: users will probably want to use the same sequence for multiple tables. In this case, we should create the sequence only once. But how to handle sequence_reset_sql? It should probably go through all the tables using the sequence and determine the maximum of all the ids in use. A simple solution for this would be to document that if you want to use the same sequence for multiple tables, Django currently can't manage the database schema for you. Note that the solution using pg_get_serial_sequence would break if the user tried to use the same sequence for multiple tables.

Still one more problem: what to do with autogenerated m2m tables. Here django currently uses table_name_id_seq as sequence name, but if I am not mistaken it is also possible for the sequence name to be longer than 63 characters here.

I think I can do the coding to get the proposed solution to work, but before starting the work it would be good to get a confirmation that the proposed solution has a chance to get accepted into 1.3.

comment:4 Changed 3 years ago by julien

  • Severity set to Normal
  • Type set to New feature

comment:5 Changed 3 years ago by jacob

#15682 was a dup.

comment:6 Changed 3 years ago by jacob

As were #13179 and #1946.

comment:7 Changed 3 years ago by jacob

  • Description modified (diff)
  • Summary changed from alternate name sequence for postgresql in class meta db_seq to Add a Meta.sequence option to models

Updated title, description.

comment:8 Changed 3 years ago by aaugustin

  • Easy pickings unset
  • UI/UX unset

See also #16448.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.