#36687: Unexpected IntegrityError and unavailability during Django Oracle
upgrades
involving AutoField/BigAutoField and pre-Django 2.0 legacy triggers
behavior
-------------------------------------+-------------------------------------
Reporter: Fabio Caritas | Type:
Barrionuevo da Luz | Uncategorized
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Release
Keywords: oracle, oracle 11, | blocker
ora-00001, AutoField, | Triage Stage:
BigAutoField, IntegrityError | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
I recently migrated a legacy project to Django 5.2 + Python 3.12 + Oracle
19c and encountered some unexpected issues.
The project started with Python 2.7 and Django 1.11 or earlier + Oracle 11
and was later migrated by someone to Django 3.0 + Oracle 12+.
== Context ==
Until Django 1.11, the Django Oracle backend created a `trigger` +
`sequence` for each `AutoField` and `BigAutoField` used in the project.
See
[https://github.com/django/django/blob/stable/1.11.x/django/db/backends/oracle/operations.py#L57-L78].
Django 2.0 introduced support for `identity` columns in `AutoField` and
`BigAutoField` on Oracle (see
[https://github.com/django/django/commit/924a89e135fe54bc7622aa6f03405211e75c06e9]),
but only for new tables/models. This means that a `trigger` + `sequence`
was no longer necessary because `identity` columns have an implicit
`sequence` created and managed automatically by Oracle. Note that support
for `identity` columns was added in Oracle 12+.
If the project started with versions prior to Django 2.0 (Django 1.11 or
lower) + Oracle 11 or lower and already has a created and populated
database, then existing tables/models will have been created with a
`trigger` + sequence for `AutoField`/`BigAutoField`.
After upgrading the codebase + database to run with Django 2.0+ and Oracle
12+, tables/models created after the upgrade will use `identity` columns,
but the old models will continue to use the `trigger` + `sequence`,
remaining fully functional and untouched.
In the future Django 6.0, the `BigAutoField` will be the default field
for primary keys.
The consequence of this is that in some situations, this results in the
creation of a new migration that will, at the database level (Oracle),
change the primary key column type from `NUMBER(11)` or `NUMBER(11)
GENERATED BY DEFAULT ON NULL AS IDENTITY` to `NUMBER(19) GENERATED BY
DEFAULT ON NULL AS IDENTITY`.
This does not cause problems for tables/models created after the project
is already using Django 2.0+ and Oracle 12+.
However, it can result in unique constraints errors, as there will now be
an `identity` column for the primary key with a `sequence` number that may
already exist in the primary key column of the table, plus a `trigger` +
`sequence` competing to generate the new value for the primary key,
randomly generating the error:
{{{
django.db.utils.IntegrityError: ORA-00001: unique constraint
(<DB_USER>.SYS_C<ID>) violated
Help: https://docs.oracle.com/error-help/db/ora-00001/
}}}
The point here is that, although the migration plan for `BigAutoField` to
be the default primary key type has been underway for some time, I could
not find any warning in the documentation about the existence of
`triggers` in Django 1.11 or lower, and I did not see or do not recall
seeing any Django warning that there would be a `trigger` and that it
could interfere with `identity` on Oracle 12+ and Django 2.0+.
Tools like `django-upgrade` and `django-codemod` also did not catch this.
== Suggestions ==
I think it would be prudent before the release of Django 6.0 to:
1. Document the behavior in the release notes for Django 2.0.
2. Document the behavior in the release notes for Django 6.0.
3. Perhaps create a migration guide to how to safe migrate from `trigger`
+ `sequence` to identity on Oracle and link it in the documentation for
`AutoField` and `BigAutoField` in all versions.
4. In the documentation for `AutoField` and `BigAutoField` in Django 2.0
and all subsequent versions, inform about the old behavior regarding the
`trigger`.
5. Perhaps improve the way migrations from `AutoField` to `BigAutoField`
are done and include the detection of the existence of the `trigger` +
sequence and inform how to resolve it.
6. Perhaps introduce some new functionality to be executed when running
`manage.py makemigrations` or `manage.py check` and thus issue a warning.
Note that Django's native application tables, such as auth, admin,
contenty_type, etc., do not generate any migrations or warnings, even with
DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField' set in the settings.
They continue to use the trigger + sequence combination if the project
born from a very old version of Django (Django 1.11 or lower + Oracle 11
or lower )
This may not be worth maintaining for the same reason that BigAutoField is
now the default primary key type on Django 6.0
A migration path/guide for these should probably be created for native
django app.
--
Ticket URL: <https://code.djangoproject.com/ticket/36687>
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 [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019a1709136d-dd044e45-e665-460b-8d6f-b4a051b450c5-000000%40eu-central-1.amazonses.com.
