Opened 10 months ago

Last modified 10 months 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 Craig de Stigter)

  1. Make a field with db_index=True (for ForeignKey, django sets this by default)
  2. Make a migration. This creates an index:
CREATE INDEX "a_c_b_id_5a026661" ON "a_c" ("b_id");
  1. Explicitly set db_index=False and create another migration
  2. 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:1 Changed 10 months ago by Craig de Stigter

A tiny test project: https://github.com/craigds/django-28339-testproject

Output with that project:

$ ./tp3/manage.py sqlmigrate a 0001
BEGIN;
--
-- Create model B
--
CREATE TABLE "a_b" ("id" serial NOT NULL PRIMARY KEY);
--
-- Create model C
--
CREATE TABLE "a_c" ("id" serial NOT NULL PRIMARY KEY, "b_id" integer NOT NULL);
ALTER TABLE "a_c" ADD CONSTRAINT "a_c_b_id_5a026661_fk_a_b_id" FOREIGN KEY ("b_id") REFERENCES "a_b" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "a_c_b_id_5a026661" ON "a_c" ("b_id");
COMMIT;
$ ./tp3/manage.py sqlmigrate a 0002
BEGIN;
--
-- Alter field b on c
--
COMMIT;
Last edited 10 months ago by Craig de Stigter (previous) (diff)

comment:2 Changed 10 months ago by Craig de Stigter

Type: UncategorizedBug

comment:3 Changed 10 months ago by Craig de Stigter

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 Changed 10 months ago by Craig de Stigter

Description: modified (diff)
Summary: Setting db_index=False on a ForeignKey doesn't actually remove the indexSetting db_index=False on a field doesn't actually remove the index

comment:5 Changed 10 months ago by Craig de Stigter

Resolution: invalid
Status: newclosed

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 Changed 10 months ago by Craig de Stigter

Resolution: invalid
Status: closednew

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 Changed 10 months ago by Tim Graham

Component: MigrationsDocumentation
Summary: Setting db_index=False on a field doesn't actually remove the indexExpand the docs around how sqlmigrate works with regards to constraint and index names
Triage Stage: UnreviewedAccepted
Type: BugCleanup/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.

Note: See TracTickets for help on using tickets.
Back to Top