Opened 5 years ago

Closed 3 years ago

Last modified 2 years ago

#30511 closed New feature (fixed)

Support Identity columns on PostgreSQL.

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

Description (last modified by Mariusz Felisiak)

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 5 years ago.
base.py (11.0 KB ) - added by Michael Kany 5 years ago.

Download all attachments as: .zip

Change History (24)

by Michael Kany, 5 years ago

Attachment: schema.py added

by Michael Kany, 5 years ago

Attachment: base.py added

comment:1 by Michael Kany, 5 years ago

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 by Mariusz Felisiak, 5 years ago

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 by Michael Kany, 5 years ago

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 by Mariusz Felisiak, 5 years ago

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 by Michael Kany, 5 years ago

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

comment:6 by Mariusz Felisiak, 5 years ago

Sure, feel-free.

comment:7 by Michael Kany, 5 years ago

Owner: changed from nobody to Michael Kany
Status: newassigned

comment:9 by Michael Kany, 5 years ago

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 by Simon Charette, 5 years ago

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 by Mariusz Felisiak, 5 years ago

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

comment:12 by Michael Kany, 5 years ago

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 by Simon Charette, 5 years ago

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 5 years ago by Simon Charette (previous) (diff)

comment:14 by Mariusz Felisiak, 5 years ago

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

comment:15 by Michael Kany, 5 years ago

Thanks a lot, I will do that.

comment:16 by nbngn, 5 years ago

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?

comment:17 by Mariusz Felisiak, 3 years ago

Owner: Michael Kany removed
Status: assignednew

comment:18 by Mariusz Felisiak, 3 years ago

Has patch: set
Owner: set to Florian Apolloner
Status: newassigned

comment:19 by Mariusz Felisiak, 3 years ago

Triage Stage: AcceptedReady for checkin

comment:20 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

Resolution: fixed
Status: assignedclosed

In 2eea361e:

Fixed #30511 -- Used identity columns instead of serials on PostgreSQL.

comment:22 by GitHub <noreply@…>, 2 years ago

In 081871b:

Refs #30511 -- Updated docs about auto-incrementing primary keys on PostgreSQL.

Follow up to 2eea361eff58dd98c409c5227064b901f41bd0d6.

comment:23 by Mariusz Felisiak <felisiak.mariusz@…>, 2 years ago

In 08907194:

[4.1.x] Refs #30511 -- Updated docs about auto-incrementing primary keys on PostgreSQL.

Follow up to 2eea361eff58dd98c409c5227064b901f41bd0d6.
Backport of 081871bc20cc8b28481109b8dcadc321e177e6be from main

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