#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 )
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)
Change History (24)
by , 5 years ago
by , 5 years ago
comment:1 by , 5 years ago
Cc: | added |
---|
comment:2 by , 5 years ago
Description: | modified (diff) |
---|---|
Summary: | AutoField with postgres10/11 as generated identity → AutoField with postgres10/11 as generated identity. |
Version: | 2.2 → master |
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 ofserial
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 , 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 , 5 years ago
Summary: | AutoField with postgres10/11 as generated identity. → Support Identity columns on PostgreSQL. |
---|---|
Triage Stage: | Unreviewed → Accepted |
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
andINCREMENT BY
.
comment:5 by , 5 years ago
Thank you for accepting this feature.
Can I now assign the ticket and make a pull request?
comment:7 by , 5 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:9 by , 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 , 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 , 5 years ago
Agreed with Simon. Let's limit this change just to swapping serial
to int GENERATED ...
on PostgreSQL 10+.
comment:12 by , 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 , 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
- https://github.com/django/django/blob/76b993a117b61c41584e95149a67d8a1e9f49dd1/django/db/backends/postgresql/base.py#L74-L75 (note that you want
bigint GENERATED ...
forBigAutoField
). - You'll probably have to adjust introspection logic and tests to have both
serial
andint GENERATED ...
map toAutoField
and do the same for thebigserial
andBigAutoField
. - 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 useALTER 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 usingDROP 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 :)
comment:14 by , 5 years ago
You can check the scope of a really similar change on Oracle 924a89e135fe54bc7622aa6f03405211e75c06e9.
comment:16 by , 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.
- 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)
- 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?
comment:17 by , 3 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
comment:19 by , 3 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
the new Fields 'IdentityAutoField': 'integer', 'IdentityBigAutoField': 'bigint', the new fields could also be implemented as AutoFiled or BigAutoField, similar to the oracle module