AutoField -> BigAutoField causes PostgreSQL sequence value changes

[Barry Johnson]

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.

[Mariusz Felisiak]

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 SERIALs and doesn't set sequence values (see 2eea361eff58dd98c409c5227064b901f41bd0d6).

Fixed by 2eea361eff58dd98c409c5227064b901f41bd0d6.