Opened 7 years ago
Last modified 7 years ago
#28339 new Cleanup/optimization
Expand the docs around how sqlmigrate works with regards to constraint and index names
Reported by: | Craig de Stigter | Owned by: | nobody |
---|---|---|---|
Component: | Documentation | Version: | 1.11 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
- Make a field with
db_index=True
(forForeignKey
, django sets this by default) - Make a migration. This creates an index:
CREATE INDEX "a_c_b_id_5a026661" ON "a_c" ("b_id");
- Explicitly set
db_index=False
and create another migration - Check
sqlmigrate
for the new migration. Index is never actually removed:
BEGIN; -- -- Alter field b on c -- COMMIT;
One note: This is with postgres settings. I'm not sure if it's db-specific.
Change History (7)
comment:2 by , 7 years ago
Type: | Uncategorized → Bug |
---|
comment:3 by , 7 years ago
Turns out, it's not just ForeignKey. Indices are not dropped on other fields too:
$ ./tp3/manage.py sqlmigrate a 0003 BEGIN; -- -- Add field charfield to b -- ALTER TABLE "a_b" ADD COLUMN "charfield" varchar(1) DEFAULT '' NOT NULL; ALTER TABLE "a_b" ALTER COLUMN "charfield" DROP DEFAULT; -- -- Add field intfield to b -- ALTER TABLE "a_b" ADD COLUMN "intfield" integer DEFAULT 0 NOT NULL; ALTER TABLE "a_b" ALTER COLUMN "intfield" DROP DEFAULT; CREATE INDEX "a_b_charfield_a5049317" ON "a_b" ("charfield"); CREATE INDEX "a_b_charfield_a5049317_like" ON "a_b" ("charfield" varchar_pattern_ops); CREATE INDEX "a_b_intfield_b730ef11" ON "a_b" ("intfield"); COMMIT;
$ ./tp3/manage.py sqlmigrate a 0004 BEGIN; -- -- Alter field charfield on b -- -- -- Alter field intfield on b -- COMMIT;
comment:4 by , 7 years ago
Description: | modified (diff) |
---|---|
Summary: | Setting db_index=False on a ForeignKey doesn't actually remove the index → Setting db_index=False on a field doesn't actually remove the index |
comment:5 by , 7 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
My apologies, this is a case of me not understanding how exactly sqlmigrate
works.
It appears that maybe Django doesn't know what the index is named ahead of time.
If you sqlmigrate
a migration that drops an index _without_ actually creating the index first in the database, the resulting SQL skips the index drop as described above.
However if you actually create the index first, then the sqlmigrate
connects to the database and inspects the schema to determine which indexes have been created. Then the resulting SQL correctly includes the index drop.
This is rather counterintuitive (to me), but perhaps it's the only possible way to do it?
Unfortunately this means I cannot convert autogenerated django migrations to SQL up-front.
comment:6 by , 7 years ago
Resolution: | invalid |
---|---|
Status: | closed → new |
On second thoughts, this is still a bug, right?
sqlmigrate above shows that the operation creating the indices _knows_ the name of the indices:
CREATE INDEX "a_b_charfield_a5049317" ON "a_b" ("charfield");
so clearly this can be predicted up-front by the migration - it shouldn't need to consult the database to list indices prior to dropping them.
comment:7 by , 7 years ago
Component: | Migrations → Documentation |
---|---|
Summary: | Setting db_index=False on a field doesn't actually remove the index → Expand the docs around how sqlmigrate works with regards to constraint and index names |
Triage Stage: | Unreviewed → Accepted |
Type: | Bug → Cleanup/optimization |
I think the constraint and index names generated by Django aren't necessarily the same across different versions -- that's probably why introspection is used. As the documentation for sqlmigrate says,
"This requires an active database connection, which it will use to resolve constraint names; this means you must generate the SQL against a copy of the database you wish to later apply it on."
See also bugs like #23577 which cause names to get out of sync with that Django initially generated (and #27064).
I'm not sure if anything can be done besides improving the documentation of these limitations.
A tiny test project: https://github.com/craigds/django-28339-testproject
Output with that project: