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 (<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.