[Django] #33483: Index name migration instability between database engines
Django
-------------------------------------+-------------------------------------
Reporter: Marti | Owner: nobody
Raudsepp |
Type: Bug | Status: new
Component: Database | Version: 4.0
layer (models, ORM) | Keywords:
Severity: Normal | oracle,migrations,indexes
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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. In Oracle 12.2 and above the maximum
object name length is 128 bytes.
=== 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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33483>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
Old description:
New description:
=== Going forward ===
officially only supports Oracle 19c and newer. In Oracle 12.2 and above
the maximum object name length is 128 bytes, not 30.
=== Reproducing ===
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33483#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
oracle,migrations,indexes | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> I have been using the `django-celery-results` project with Django on an
> officially only supports Oracle 19c and newer. In Oracle 12.2 and above
> the maximum object name length is 128 bytes, not 30.
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
=== Going forward ===
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33483#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
oracle,migrations,indexes | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Marti Raudsepp):
There is a simple work-around for this issue: explicitly specify index
names in models, e.g.
{{{
#!python
class Foo(models.Model):
...
class Meta:
indexes = [
models.Index(fields=['date_created'],
name='django_cele_date_cr_bd6c1d_idx'),
]
}}}
Opened a pull request for `django-celery-results`:
https://github.com/celery/django-celery-results/pull/283/files
--
Ticket URL: <https://code.djangoproject.com/ticket/33483#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
oracle,migrations,indexes | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old 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
> 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.
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
=== Going forward ===
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':
.../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':
.../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':
.../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:4>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 4.0
(models, ORM) |
Keywords: | Triage Stage:
oracle,migrations,indexes | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
Thanks for the report.
> I think this is a bug in Django -- generally the expectation seems to be
that migration files are portable between databases.
Unfortunately, database backends (even builtin) will never be fully
swappable.
> 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.
I think it is not a feasible option, see #30684. Moreover this
[https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf
/Database-Object-Names-and-Qualifiers.html#GUID-75337742-67FD-4EC0-985F-
741C93D918DA can be changed] via `COMPATIBLE` parameter.
> 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.
IMO it's not doable, because we don't have a clean and backward compatible
path, basically from the same reason as #30684. I would recommend adding
`db_table` (as you did for `django-celery-results`) or using a
[https://docs.djangoproject.com/en/4.0/ref/databases/#subclassing-the-
built-in-database-backends backend subclass] to override
`max_name_length()` in your own project.
I really would like to unify this, however we cannot do this without a
backward compatible solution.
--
Ticket URL: <https://code.djangoproject.com/ticket/33483#comment:5>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: | Triage Stage:
oracle,migrations,indexes | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Marti Raudsepp):
> I really would like to unify this, however we cannot do this without a
backward compatible solution.
I agree, backwards compatibility is important. Isn't this the right place
to come up with options how to do it in a backward-compatible manner?
But you seem to be shutting down the path forward with "it's difficult,
therefore we won't fix it".
> Unfortunately, database backends (even builtin) will never be fully
swappable.
I didn't say "fully swappable", I recognize there are engine-specific
features.
But for models that use bare minimum ORM features (tables, columns,
indexes), isn't that something to strive for? There are so many Django
libraries and add-ons that rely on this assumption. This is one of the
most valuable parts of the Django ecosystem. Throwing it out is not
something the Django project should do lightly.
Otherwise, the fact that migrations aren't portable between engines should
be clearly documented, which it is not as far as I can tell.
--
Ticket URL: <https://code.djangoproject.com/ticket/33483#comment:6>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: | Triage Stage:
oracle,migrations,indexes | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
> Isn't this the right place to come up with options how to do it in a
backward-compatible manner?
I cannot imagine any way to achieve this, so if you have any proposition
I'm all ears.
--
Ticket URL: <https://code.djangoproject.com/ticket/33483#comment:7>