[Django] #30511: AutoField with postgres10/11 as generated identity

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

[Django] #30511: AutoField with postgres10/11 as generated identity

Django
#30511: AutoField with postgres10/11 as generated identity
-------------------------------------+-------------------------------------
               Reporter:  Michael    |          Owner:  nobody
  Kany                               |
                   Type:  New        |         Status:  new
  feature                            |
              Component:  Database   |        Version:  2.2
  layer (models, ORM)                |       Keywords:  postgres generated
               Severity:  Normal     |  identity
           Triage Stage:             |      Has patch:  0
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 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

--
Ticket URL: <https://code.djangoproject.com/ticket/30511>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/053.85058bd127809d3e64647858831c7797%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: AutoField with postgres10/11 as generated identity

Django
#30511: AutoField with postgres10/11 as generated identity
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  2.2
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:
  identity                           |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Michael Kany):

 * Attachment "schema.py" added.


--
Ticket URL: <https://code.djangoproject.com/ticket/30511>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.b3a4221f3e92413d4c5648c1635fde19%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: AutoField with postgres10/11 as generated identity

Django
In reply to this post by Django
#30511: AutoField with postgres10/11 as generated identity
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  2.2
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:
  identity                           |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Michael Kany):

 * Attachment "base.py" added.


--
Ticket URL: <https://code.djangoproject.com/ticket/30511>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.8b3a6d952ac741a1c11d2278eaa167d7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: AutoField with postgres10/11 as generated identity

Django
In reply to this post by Django
#30511: AutoField with postgres10/11 as generated identity
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  2.2
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:
  identity                           |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Michael Kany):

 * cc: Michael Kany (added)


Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.25eced313d0fd6dae1b2597fbb52c4da%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: AutoField with postgres10/11 as generated identity. (was: AutoField with postgres10/11 as generated identity)

Django
In reply to this post by Django
#30511: AutoField with postgres10/11 as generated identity.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:
  identity                           |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by felixxm):

 * version:  2.2 => master


Old description:

> 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
New description:

 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

--

Comment:

 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.ea4f352abbe31e9b260855435c394b77%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: AutoField with postgres10/11 as generated identity.

Django
In reply to this post by Django
#30511: AutoField with postgres10/11 as generated identity.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:
  identity                           |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (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.

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.04986717e5dc3bcf1290212b2269ce19%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL. (was: AutoField with postgres10/11 as generated identity.)

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by felixxm):

 * stage:  Unreviewed => Accepted


Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.62b9ebc792a6fc655cdc89d2d2461cbd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Michael Kany):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:5>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.20a3a740cc5a7cd1c007b6a63f93c0bf%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by felixxm):

 Sure, feel-free.

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:6>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.18c0b87db6d6c082c30a1b8198a70152%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Michael Kany):

 * owner:  nobody => Michael Kany
 * status:  new => assigned


--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:7>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.202a25f7ffaf1170229a2f11c7dc224a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Michael Kany):

 Ich habe gesehen, das es anstelle der vorgeschlagenene zwei neuen Field
 classes ('IdentityAutoField': 'integer', 'IdentityBigAutoField':
 'bigint',)
 Ist es möglich die AutoField-klasse so zu erweitern
 Zum beispiel mit:

 class AutoField(Field):
 ...
 generated_identity = False  # für die unterscheidung serial oder generated
 generated_by_default = False # für GENERATED ALWAYS wenn False/ GENERATED
 BY DEFAULT wenn 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):
 ...

 Da ist auch ein anderes Ticket
 #56     Primary key columns should be UNSIGNED
 das mit den Parametern
 seq_opt_start_with = 1
 seq_opt_increment_by = 1
 gegebenenfalls  passen würde. Test ob seq_opt_start_with >= 0

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:8>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.8091b4d5dc7f79189f299bfbd8dc09e6%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (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

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:9>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.281678674bb7c9d34f1d9d67f18a1712%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (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
 [https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/ 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:10>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.a67fc30d12d1c8090f85624bf34bbbfd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by felixxm):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:11>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.f5917ffc5936ab10ca5fb912f3a11848%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (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.

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:12>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.b18fd3762c0bdddad3d47299288945ce%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

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

 1.
 https://github.com/django/django/blob/76b993a117b61c41584e95149a67d8a1e9f49dd1/django/db/backends/postgresql/base.py#L74-L75
 (not 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
 [https://github.com/django/django/blob/1378d665a1c85897d951f2ca9618b848fdbba2e7/django/db/backends/postgresql/schema.py#L9-L10
 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 :)

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:13>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.8fa74d06520470f34149c378c2f02802%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by felixxm):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:14>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.4b81aa06cecad25d1b38bb2395be2b44%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Michael Kany):

 Thanks a lot, I will do that.

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:15>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.35679ec0f650d5074eee08b4efb145dd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30511: Support Identity columns on PostgreSQL.

Django
In reply to this post by Django
#30511: Support Identity columns on PostgreSQL.
-------------------------------------+-------------------------------------
     Reporter:  Michael Kany         |                    Owner:  Michael
                                     |  Kany
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  postgres generated   |             Triage Stage:  Accepted
  identity                           |
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (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:
 [https://github.com/django/django/blob/7254f1138d9c51fa558229c39c9559b369c4278a/django/db/backends/postgresql/features.py#L68
 has_brin_autosummarize in db\backends\postgresql\features.py]
 [https://github.com/django/django/blob/85ac838d9e6975130b5c55299e9d7d1222a8e289/django/contrib/postgres/indexes.py#L57
 check_support has_brin_autosummarize in contrib\postgres\indexes.py]


 {{{
 #!python
 # 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?

--
Ticket URL: <https://code.djangoproject.com/ticket/30511#comment:16>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/068.97d42e76e794ffff4d7ad36b598d8823%40djangoproject.com.