Fix ordering and conflict issues with adding and dropping indexes.
Review Request #12280 — Created May 14, 2022 and submitted
When switching between
db_index
andunique
states, or when dropping
indexes due todb_index=False
while performing other changes to the
model, we'd run into a couple issues. We could end up trying to create
duplicate indexes, drop indexes too late, or create standard and
unique indexes in one go.There were two reasons for these problems, one common to all databases,
and one SQLite-specific.The common issue was that we handled
db_index
andunique
changes
separately. If enablingdb_index
andunique
in the same batch of
changes, we could get indexes for both. If disabling both, we could get
redundant drops. If switching from one to the other, we could get
ordering issues or accidental drops or adds of an index.This is resolved by considering
db_index
andunique
changes
together, and ensuring that we never add or drop two types of indexes
at once or put them in the wrong order. The logic here is common to all
databases.SQLite had a specialized issue caused by the fact that we have to
rebuild tables and copy data in order to modify field state. Since this
process is expensive, we batch together the changes we need to make and
then generate the appropriate SQL at execution time. This process ends
up causing us to drop indexes that are no longer needed and to rebuild
the indexes we do want in the resulting table.The common logic for adding or deleting standard indexes didn't work
well with this. We'd just output the SQL, which could result in an index
being dropped after a table rebuild (when the index no longer exists
anyway) or being added after the rebuild (when it already was added as
part of generation).This issue only applies to
db_index
changes, sinceunique
already
triggered a table rebuild.The fix here is to have
db_index
changes trigger a possible rebuild as
well, instead of immediately outputting SQL. The new generation ops for
this (ADD DB INDEX
andDROP DB INDEX
) don't themselves trigger a
rebuild. If nothing else is being rebuilt, the appropriate SQL will be
added as normal, but in the right order. If there is a table rebuild,
any drops will take place before the rebuild, and any adds after
(technically, they'll already be added, so we just stuff the information
back into theDatabaseState
for index caching).New unit tests for the various combinations of states have been added.
Unit tests pass for all versions of Django on all supported types of
databases.Tested along with the Review Board 4 -> 5 database upgrade, which was
hitting this bug.