#33483: Index name migration instability between database engines
-------------------------------------+-------------------------------------
Reporter: Marti Raudsepp | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
oracle,migrations,indexes | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Marti Raudsepp:
Old description:
> I have been using the `django-celery-results` project with Django on an
> Oracle database. We 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`.
>
> By running `makemigrations` on each in turn, they generate new
> migrations.
>
> {{{
> % 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
> }}}
New description:
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
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33483#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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/063.fc0c9a8ea9f42bb845fda6a4e787c071%40djangoproject.com.
