[Django] #33941: AutoField -> BigAutoField causes PostgreSQL sequence value changes
Django
-------------------------------------+-------------------------------------
Reporter: Barry | Owner: nobody
Johnson |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: BigAutoField,
Severity: Normal | sequence
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When changing a primary key from AutoField to BigAutoField, the underlying
PostgreSQL sequence may also have to be changed from type "integer" to
"big integer". Django automatically makes that change by dropping and
recreating the sequence, then sets the sequence's value to the max value
of the primary keys currently in the table.
There are use cases where this reset of the sequence value is problematic.
Consider the case of a "work in progress" table whose primary key is
considered the unique identifier for each entity, and the contents of that
work-in-progress table are eventually moved to a historical log table (and
removed from the original table). Should the WIP table be empty when its
primary key is changed from AutoField to BigAutoField, then the sequence
value will be set back to a Null and the next WIP record will have a
primary key of 1 -- duplicating a key previously recorded in the log
table. The "always incrementing" sequence will be reset to a lower value
any time the most recently added record to the WIP table has been deleted
prior to the key change -- and that sudden decrease in value has caused
downstream application failures.
The request is to have Django preserve the value of the sequence across
the drop and recreate steps and avoid this loss of information. If that
change is not acceptable, the fallback request is to document the
behavior.
This is particularly subtle problem because sequences are relatively
invisible to most ORM programmers. Unless they carefully watch the SQL
executed during a migration, they wouldn't really be aware that the
sequence is being dropped and recreated -- and particularly unaware that
the sequence could be reset to a smaller value -- they only discover the
problem after reports of duplicate key values when new WIP rows are
eventually transferred to the log table.
--
Ticket URL: <https://code.djangoproject.com/ticket/33941>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: BigAutoField, | Triage Stage:
sequence | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
Thanks for this ticket, however Django 3.2 is in extended support and
doesn't receive bugfixes anymore (except security patches). Moreover,
Django 4.1+ should not be affected anymore as it uses `IDENTITY` columns
instead of `SERIAL`s and doesn't set sequence values (see
2eea361eff58dd98c409c5227064b901f41bd0d6).
Fixed by 2eea361eff58dd98c409c5227064b901f41bd0d6.
--
Ticket URL: <https://code.djangoproject.com/ticket/33941#comment:1>