Opened 15 months ago

Last modified 11 months ago

#30511 assigned New feature

Support Identity columns on PostgreSQL.

Reported by: Michael Kany Owned by: Michael Kany
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: postgres generated identity
Cc: Michael Kany Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by felixxm)

We are currently working on a Django / Postgresql 11 project. We needed an adjustment for the postgres module because of its compatibility with .NET Framework and dataset generation.
In postgres 10/11 there is the new feature identity column called GENERATED AS IDENTITY instead of serial.
column_name type GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(sequence_option)]
We have created a module based on db.backends.postgres that adds 2 new django model fields. I would like implement the new feature into pyodbc module or new module for postgres 10/11 useful for later versions of django.

Adjustments only in base.py (line 70ff):

data_types = {
        'IdentityAutoField': 'integer',
        'IdentityBigAutoField': 'bigint',

and schema.py, def column_sql () (line 178ff):

# Primary key / unique outputs
        if field.primary_key:
            sql + = "PRIMARY KEY"
            if field.get_internal_type () in ("IdentityAutoField", "IdentityBigAutoField"):
                sql + = "GENERATED ALWAYS AS IDENTITY"
        elif field.unique:
            sql + = "UNIQUE"

I can also send our code

best regards
Michael Kany

Attachments (2)

schema.py (12.6 KB) - added by Michael Kany 15 months ago.
base.py (11.0 KB) - added by Michael Kany 15 months ago.

Download all attachments as: .zip

Change History (17)

Changed 15 months ago by Michael Kany

Attachment: schema.py added

Changed 15 months ago by Michael Kany

Attachment: base.py added

comment:1 Changed 15 months ago by Michael Kany

Cc: Michael Kany added

the new Fields 'IdentityAutoField': 'integer', 'IdentityBigAutoField': 'bigint', the new fields could also be implemented as AutoFiled or BigAutoField, similar to the oracle module

comment:2 Changed 15 months ago by felixxm

Description: modified (diff)
Summary: AutoField with postgres10/11 as generated identityAutoField with postgres10/11 as generated identity.
Version: 2.2master

Thanks for the report. Can you provide more details about your use case? Can you also clarify what are you proposing? e.g.

  • using GENERATED AS IDENTITY instead of serial on PostgreSQL (I don't see much value in this),
  • adding extra fields to the PostgreSQL backends.

In Oracle we used GENERATED AS IDENTITY because there is no other way to create serial-like columns on Oracle which is not the case on PostgreSQL.

comment:3 Changed 15 months ago by Michael Kany

I agree that there is not much value in replacing serial with GENERATED AS IDENTITY.

What I suggest is to add extra fields related to GENERATED AS IDENTITY to the PostgreSQL backends as an alternative option to SERIAL since it is an option in PostgreSQL and it is actually SQL-standard compliant compared to SERIAL.

Since PostgreSQL 10 there is the option to use GENERATED AS IDENTITY, I would like to suggest to make Django compatible with this feature by adding two extra AutoField-like fields.

  • IdentityAutoField with inner workings like AutoField with SERIAL but with GENERATED AS IDENTITY
  • IdentityBigAutoField with inner workings like BigAutoField with SERIAL but with GENERATED AS IDENTITY

These fields would probably be interesting only for Postgres user.

comment:4 Changed 15 months ago by felixxm

Summary: AutoField with postgres10/11 as generated identity.Support Identity columns on PostgreSQL.
Triage Stage: UnreviewedAccepted

I tentatively accept this feature. IMO in basic implementation it doesn't have much value, but it can be helpful if we support all options:

  • GENERATED ALWAYS / GENERATED BY DEFAULT , and
  • sequence options START WITH and INCREMENT BY.

comment:5 Changed 15 months ago by Michael Kany

Thank you for accepting this feature.
Can I now assign the ticket and make a pull request?

comment:6 Changed 15 months ago by felixxm

Sure, feel-free.

comment:7 Changed 15 months ago by Michael Kany

Owner: changed from nobody to Michael Kany
Status: newassigned

comment:9 Changed 14 months ago by Michael Kany

sorry,
I've seen that instead of the proposed two new Field classes ('IdentityAutoField': 'integer', 'IdentityBigAutoField': 'bigint',)
Is it possible to extend the AutoField class like this?
For example with:

class AutoField (Field):
...
generated_identity = False # for the serial or generated distinction
generated_by_default = false # for GENERATED ALWAYS if false / GENERATED BY DEFAULT if true
seq_opt_start_with = 1
seq_opt_increment_by = 1

def init (self, generated_identity = false, generated_by_default = false, seq_opt_start_with = 1, seq_opt_increment_by = 1, kwargs):
...

There is another ticket
# 56 Primary key columns should be UNSIGNED
that with the parameters
seq_opt_start_with = 1
seq_opt_increment_by = 1
would fit if necessary. Test if seq_opt_start_with> = 0

comment:10 Changed 14 months ago by Simon Charette

FWIW I think that we shouldn't be introducing a new field for this but instead swap AutoField to use int GENERATED BY DEFAULT AS IDENTITY instead of serial on PostgreSQL 10+.

From Django's perspective both behave the same anyway and I think the cases where other IDENTITY options make sense are very limited or cause conflicts with the way the ORM works which makes me believe we shouldn't support them even in contrib.postgres. For example, GENERATED ALWAYS AS IDENTITY would completely ignore any value provided for the field which differs from all the other fields behavior.

I guess we could also include an update script in the release notes for users that want to avoid having a mix of serial and IDENTITY primary keys.

Just my 2 cents.

comment:11 Changed 14 months ago by felixxm

Agreed with Simon. Let's limit this change just to swapping serial to int GENERATED ... on PostgreSQL 10+.

comment:12 Changed 14 months ago by Michael Kany

Ok, let's limit this to swapping serial to int GENERATED ... on pg10+. Where should I do that?
contrib.postgres or db.backends.postgresql.

comment:13 Changed 14 months ago by Simon Charette

Michael, I haven't look into all the details but I'm confident you'll have to at least to change the following

  1. https://github.com/django/django/blob/76b993a117b61c41584e95149a67d8a1e9f49dd1/django/db/backends/postgresql/base.py#L74-L75 (note that you want bigint GENERATED ... for BigAutoField).
  2. You'll probably have to adjust introspection logic and tests to have both serial and int GENERATED ... map to AutoField and do the same for the bigserial and BigAutoField.
  3. You might have to adjust some migration code as I see it performs some sequence creation and deletion. I guess you'll have to change the create_sequence usage in _alter_column_type_sql to use ALTER COLUMN %(column) ADD GENERATED BY DEFAULT AS IDENTITY on PostgreSQL 10+. Note at in this particular case I think we should keep dropping the sequence using DROP SEQUENCE IF EXISTS ... CASCADE even on PostgreSQL 10+ at least for one release after we drop support for PostgreSQL < 10 to avoid orphaning sequences during Django and PostgreSQL upgrades.

That should get you started :)

Last edited 14 months ago by Simon Charette (previous) (diff)

comment:14 Changed 14 months ago by felixxm

You can check the scope of a really similar change on Oracle 924a89e135fe54bc7622aa6f03405211e75c06e9.

comment:15 Changed 14 months ago by Michael Kany

Thanks a lot, I will do that.

comment:16 Changed 11 months ago by nbngn

Hi, I am also interested in having IDENTITY as Autofield in PostgreSQL. I have attempted to implement it the past few weeks according to the specifications mentioned here but it's not so easy.

  1. I was only able to write an exception for checking if the PostgreSQL version is >=10. I built it like the check if brin_autosummarize is allowed. Which is I will push once I have the rest of code with IDENTITY implemented (which will take a while). What are the requirements to drop support for PostgreSQL < 10?

Built similarly to:
has_brin_autosummarize in db\backends\postgresql\features.py
check_support has_brin_autosummarize in contrib\postgres\indexes.py

# in db\backends\postgresql\base.py:
def check_supported(self):
        """
        Using IDENTITY is supported in since Django 3.0, PostgreSQL>=10. 
        Using SERIAL is supported in Django Version<3.0, PostgreSQL<10.
        """
        if not self.features_class.changed_serial_to_identity:
            raise NotSupportedError('AutoField requires PostgreSQL 10+. Using SERIAL is supported in Django Version<3.0.')


# in db\backends\postgresql\features.py:    
       changed_serial_to_identity = property(operator.attrgetter('is_postgresql_10'))


# in tests\backends\postgresql\tests.py
    def test_database_supported_version(self):
        """
        Fundamental changes in AutoField requires PostgreSQL 10+. 
        SQL-compliant GENERATED BY IDENTITY is supported instead of SERIAL.
        """
        from django.db.backends.postgresql.base import DatabaseWrapper
        new_connection = connection.copy() 
        db_wrapper = DatabaseWrapper(new_connection.settings_dict)
        unsupported_version = new_connection.pg_version < 100000

        # Test unsupported version
        msg = 'AutoField requires PostgreSQL 10+. Using SERIAL is supported in Django Version<3.0.'
        with self.assertRaisesMessage(NotSupportedError, msg):
            db_wrapper.features_class.changed_serial_to_identity = unsupported_version
            db_wrapper.check_supported()

        # After test should reset back to original state 
        db_wrapper.features_class.changed_serial_to_identity = property(operator.attrgetter('db_wrapper.features_class.is_postgresql_10'))
        self.assertTrue(db_wrapper.features_class.changed_serial_to_identity != unsupported_version)
  1. List of where changes need to be made that I have found so far:
locationmethodnecessary changes
django/db/backends/postgresql/base.py DatabaseWrapper.data_types* serial → int GENERATED BY DEFAULT AS IDENTITY
* bigserial → bigint GENERATED BY DEFAULT AS IDENTITY
* smallserial → smallint GENERATED BY DEFAULT AS IDENTITY
django/db/backends/postgresql/operations.py DatabaseOperations.sequence_reset_by_name_sql (pg_get_serial_sequence)need to write a function to find the sequence of an identity field
django/db/backends/postgresql/operations.py DatabaseOperations.sequence_reset_sql (pg_get_serial_sequence)need to write a function to find the sequence of an identity field
django/db/backends/postgresql/schema.py DatabaseSchemaEditor._alter_column_type_sqlalterations for IDENTITY

Found unit-test locations:

location
django/tests/db/backends/base/
django/tests/db/backends/postgresql/
django/tests/schema/
django/tests/postgres_tests/

I think for the sequences no real changes need to be made. Just all with the SERIAL-only parts. Are these all locations?

Note: See TracTickets for help on using tickets.
Back to Top