Index name migration instability between database engines

[Marti Raudsepp]

I have been using the django-celery-results project with Django on an Oracle database. I had issues upgrading to 2.1+ versions. (One of those issues was fixed in django-celery-results 2.2.0, the other issue remained: ​https://github.com/celery/django-celery-results/issues/222)

Whenever running makemigrations with the newer version, it would attempt to create a new migration file to drop all indexes and recreate them with a different name. This happens because 2.1.0 introduced some AddIndex migrations.

I tracked this down to the index name generation logic in Index.set_name_with_model(): ​https://github.com/django/django/blob/4.0.2/django/db/models/indexes.py#L150-L163

The index name hash depends on model._meta.db_table, which differs depending on the database.

• PostgreSQL: hash_data=['django_celery_results_taskresult', 'task_name', 'idx'] -> index name django_cele_task_na_08aec9_idx
• Oracle: hash_data=['django_celery_results_task5a9b', 'task_name', 'idx'] -> index name django_cele_task_na_4bcd60_idx

Looking at DatabaseOperations.max_name_length() methods, this issue potentially affects all supported engines. But it's much more likely to affect Oracle due to the historically low identifier limit of 30 characters.

I think this is a bug in Django -- generally the expectation seems to be that migration files are portable between databases.

Going forward

If the logic in Index.set_name_with_model() were to change -- for example to use un-truncated table name -- then after upgrading to that Django version, Django migrations out of the box would re-create all these indexes. For users with large databases, recreating indexes may cause significant disruption.

So some compatibility logic for legacy index names seems warranted. Although for my personal use case, I wouldn't mind a fix that requires such "flag-day" migrations.

On the other hand, it would be nice to bump Oracle's max_name_length() anyway. The current 30-character limit is already obsolete. Django 4.0 officially only supports Oracle 19c and newer. In Oracle 12.2 and above the maximum object name length is 128 bytes, not 30.

Reproducing

I reduced this down to two clean Django 4.0.2 setups, the only changes are adding INSTALLED_APPS django_celery_results, and configuring DATABASES for Oracle and PostgreSQL respectively.

By running makemigrations on each in turn, they generate new migrations every time.

% python django-celery-results-postgres/manage.py makemigrations
No changes detected
% python django-celery-results-oracle/manage.py makemigrations
Migrations for 'django_celery_results':
  /Users/marti.raudsepp/own/django-celery-results/django_celery_results/migrations/0011_remove_groupresult_django_cele_date_cr_bd6c1d_idx_and_more.py
    - Remove index django_cele_date_cr_bd6c1d_idx from groupresult
    - Remove index django_cele_date_do_caae0e_idx from groupresult
    - Remove index django_cele_task_na_08aec9_idx from taskresult
    - Remove index django_cele_status_9b6201_idx from taskresult
    - Remove index django_cele_worker_d54dd8_idx from taskresult
    - Remove index django_cele_date_cr_f04a50_idx from taskresult
    - Remove index django_cele_date_do_f59aad_idx from taskresult
    - Create index django_cele_date_cr_0d69cb_idx on field(s) date_created of model groupresult
    - Create index django_cele_date_do_030540_idx on field(s) date_done of model groupresult
    - Create index django_cele_task_na_4bcd60_idx on field(s) task_name of model taskresult
    - Create index django_cele_status_9c2623_idx on field(s) status of model taskresult
    - Create index django_cele_worker_d87a3d_idx on field(s) worker of model taskresult
    - Create index django_cele_date_cr_2b7193_idx on field(s) date_created of model taskresult
    - Create index django_cele_date_do_c4aa5e_idx on field(s) date_done of model taskresult
% python django-celery-results-postgres/manage.py makemigrations
Migrations for 'django_celery_results':
  /Users/marti.raudsepp/own/django-celery-results/django_celery_results/migrations/0012_remove_groupresult_django_cele_date_cr_0d69cb_idx_and_more.py
    - Remove index django_cele_date_cr_0d69cb_idx from groupresult
[...]
    - Create index django_cele_date_do_f59aad_idx on field(s) date_done of model taskresult
% python django-celery-results-oracle/manage.py makemigrations
Migrations for 'django_celery_results':
  /Users/marti.raudsepp/own/django-celery-results/django_celery_results/migrations/0013_remove_groupresult_django_cele_date_cr_bd6c1d_idx_and_more.py
    - Remove index django_cele_date_cr_bd6c1d_idx from groupresult
[...]
    - Create index django_cele_date_do_c4aa5e_idx on field(s) date_done of model taskresult