Fix ordering and conflict issues with adding and dropping indexes.

Review Request #12280 — Created May 14, 2022 and submitted — Latest diff uploaded


Django Evolution


When switching between db_index and unique states, or when dropping
indexes due to db_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 and unique changes
separately. If enabling db_index and unique 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 and unique 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

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, since unique 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 and DROP 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 the DatabaseState 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

Tested along with the Review Board 4 -> 5 database upgrade, which was
hitting this bug.