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
33941	AutoField -> BigAutoField causes PostgreSQL sequence value changes	Barry Johnson	nobody	"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."	Bug	closed	Database layer (models, ORM)	3.2	Normal	fixed	BigAutoField, sequence		Unreviewed	0	0	0	0	0	0