Widening AutoField to BigAutoField, fails to widen the sequence.

[Anders Kaseorg]

#30511 changed the behavior of migrations that widen AutoField, removing the code that updates the type of the corresponding sequence. This creates a problem in projects that were upgraded from Django 4.0 or earlier, where the AutoFields will still be serial columns, and then migrated in Django 4.1, where the sequence will not be updated automatically.

Here’s a self-contained reproduction recipe:

1. Install an old version of Django:

   $ pip install django==4.0.7 psycopg2-binary
   

2. Create a minimal test project:

   # manage.py
   import os, sys
   from django.core.management import execute_from_command_line
   os.environ.setdefault("DJANGO_SETTINGS_MODULE", "my_settings")
   execute_from_command_line(sys.argv)
   

   # my_settings.py
   DATABASES = {
       "default": {
           "ENGINE": "django.db.backends.postgresql",
           "NAME": "my_database",
       }
   }
   INSTALLED_APPS = ["my_app"]
   

   # my_app/models.py
   from django.db import models
   class Widget(models.Model):
       id = models.SmallAutoField(primary_key=True)
   

3. Create the PostgreSQL database and run migrations:

   $ createdb my_database
   $ python manage.py makemigrations my_app 
   Migrations for 'my_app':
     my_app/migrations/0001_initial.py
       - Create model Widget
   $ python manage.py migrate
   Operations to perform:
     Apply all migrations: my_app
   Running migrations:
     Applying my_app.0001_initial... OK
   

4. Upgrade to Django 4.1:

   $ pip install Django==4.1
   

5. Edit my_app/models.py to widen the field from SmallAutoField to BigAutoField:

   # my_app/models.py
   from django.db import models
   class Widget(models.Model):
       id = models.BigAutoField(primary_key=True)
   

6. Run migrations:

   $ ./manage.py makemigrations
   Migrations for 'my_app':
     my_app/migrations/0002_alter_widget_id.py
       - Alter field id on widget
   $ ./manage.py migrate
   Operations to perform:
     Apply all migrations: my_app
   Running migrations:
     Applying my_app.0002_alter_widget_id... OK
   

7. Observe that the field has been widened but the sequence corresponding to it has not:

   $ ./manage.py dbshell
   psql (14.5, server 13.8)
   Type "help" for help.
   
   my_database=> \d my_app_widget
                               Table "public.my_app_widget"
    Column |  Type  | Collation | Nullable |                  Default                  
   --------+--------+-----------+----------+-------------------------------------------
    id     | bigint |           | not null | nextval('my_app_widget_id_seq'::regclass)
   Indexes:
       "my_app_widget_pkey" PRIMARY KEY, btree (id)
   
   my_database=> \d my_app_widget_id_seq
                  Sequence "public.my_app_widget_id_seq"
      Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache 
   ----------+-------+---------+---------+-----------+---------+-------
    smallint |     1 |       1 |   32767 |         1 | no      |     1
   Owned by: public.my_app_widget.id
   

8. Try and fail to create a large number of rows:

   $ ./manage.py shell
   Python 3.10.6 (main, Aug  1 2022, 20:38:21) [GCC 11.3.0] on linux
   Type "help", "copyright", "credits" or "license" for more information.
   (InteractiveConsole)
   >>> from my_app.models import Widget
   >>> Widget.objects.bulk_create(Widget() for i in range(40000))
   Traceback (most recent call last):
     File "…/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
       return self.cursor.execute(sql, params)
   psycopg2.errors.SequenceGeneratorLimitExceeded: nextval: reached maximum value of sequence "my_app_widget_id_seq" (32767)
   
   
   The above exception was the direct cause of the following exception:
   Traceback (most recent call last):
     File "<console>", line 1, in <module>
   …
     File "…/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
       return self.cursor.execute(sql, params)
   django.db.utils.DataError: nextval: reached maximum value of sequence "my_app_widget_id_seq" (32767)
   

[Mariusz Felisiak]

Thanks for the report! Regression in 2eea361eff58dd98c409c5227064b901f41bd0d6.

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In 19e6efa5:

Fixed #34058 -- Changed sequence types when altering pre-Django 4.1 auto fields on PostgreSQL.

Thanks Anders Kaseorg for the report.

Thanks Florian Apolloner for pair programming.

Regression in 2eea361eff58dd98c409c5227064b901f41bd0d6.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 97353bc6:

[4.1.x] Fixed #34058 -- Changed sequence types when altering pre-Django 4.1 auto fields on PostgreSQL.

Thanks Anders Kaseorg for the report.

Thanks Florian Apolloner for pair programming.

Regression in 2eea361eff58dd98c409c5227064b901f41bd0d6.
Backport of 19e6efa50b603af325e7f62058364f278596758f from main

[Anders Kaseorg]

I found a scenario in which this fix doesn’t work: if the field had been renamed before the upgrade, the corresponding sequence will not have been renamed.

To modify my above reproduction recipe, insert a new step between steps 3 and 4 renaming the field:

# my_app/models.py
from django.db import models
class Widget(models.Model):
    renamed_id = models.SmallAutoField(primary_key=True)

$ ./manage.py makemigrations
Was widget.id renamed to widget.renamed_id (a SmallAutoField)? [y/N] y
Migrations for 'my_app':
  my_app/migrations/0002_rename_id_widget_renamed_id.py
    - Rename field id on widget to renamed_id
$ ./manage.py migrate
Operations to perform:
  Apply all migrations: my_app
Running migrations:
  Applying my_app.0002_rename_id_widget_renamed_id... OK

(and then keep the new name in step 5).

[Mariusz Felisiak]

Replying to Anders Kaseorg:

I found a scenario in which this fix doesn’t work: if the field had been renamed before the upgrade, the corresponding sequence will not have been renamed.

To modify my above reproduction recipe, insert a new step between steps 3 and 4 renaming the field:

# my_app/models.py
from django.db import models
class Widget(models.Model):
    renamed_id = models.SmallAutoField(primary_key=True)

$ ./manage.py makemigrations
Was widget.id renamed to widget.renamed_id (a SmallAutoField)? [y/N] y
Migrations for 'my_app':
  my_app/migrations/0002_rename_id_widget_renamed_id.py
    - Rename field id on widget to renamed_id
$ ./manage.py migrate
Operations to perform:
  Apply all migrations: my_app
Running migrations:
  Applying my_app.0002_rename_id_widget_renamed_id... OK

(and then keep the new name in step 5).

Good catch! It's partly ​a long-standing issue, however, IMO, we should backport ​the fix.

[GitHub <noreply@…>]

In bc3b8f1:

Refs #34058 -- Fixed changing/deleting sequences when altering pre-Django 4.1 auto fields on PostgreSQL.

Thanks Anders Kaseorg for the report.

Follow up to 19e6efa50b603af325e7f62058364f278596758f.
Regression in 2eea361eff58dd98c409c5227064b901f41bd0d6.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 96c541e:

[4.1.x] Refs #34058 -- Fixed changing/deleting sequences when altering pre-Django 4.1 auto fields on PostgreSQL.

Thanks Anders Kaseorg for the report.

Follow up to 19e6efa50b603af325e7f62058364f278596758f.
Regression in 2eea361eff58dd98c409c5227064b901f41bd0d6.

Backport of bc3b8f152452ba0e41f28baa93c0bf8f39cddb09 from main