Support Identity columns on PostgreSQL.

[Michael Kany]

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

[Michael Kany]

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

[Mariusz Felisiak]

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.

[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.

[Mariusz Felisiak]

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.

[Michael Kany]

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

[Mariusz Felisiak]

Sure, feel-free.

[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

[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.

[Mariusz Felisiak]

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

[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.

[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 :)

[Mariusz Felisiak]

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

[Michael Kany]

Thanks a lot, I will do that.

[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)

2. List of where changes need to be made that I have found so far:

location	method	necessary 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_sql	alterations 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?

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 2eea361e:

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

[Mariusz Felisiak]

#33941 was fixed by 2eea361eff58dd98c409c5227064b901f41bd0d6.

[GitHub <noreply@…>]

In 081871b:

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

Follow up to 2eea361eff58dd98c409c5227064b901f41bd0d6.

[Mariusz Felisiak <felisiak.mariusz@…>]

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