id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 36687 Unexpected IntegrityError and unavailability during Django Oracle upgrades involving AutoField/BigAutoField and pre-Django 2.0 legacy triggers behavior Fabio Caritas Barrionuevo da Luz "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 (.SYS_C) 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. " Uncategorized closed Database layer (models, ORM) 6.0 Normal needsinfo oracle, oracle 11, ora-00001, AutoField, BigAutoField, IntegrityError Unreviewed 0 0 0 0 0 0