Expand the docs around how sqlmigrate works with regards to constraint and index names

[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");

3. Explicitly set db_index=False and create another migration
4. 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.

[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;

[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;

[Craig de Stigter]

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.

[Craig de Stigter]

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.

[Tim Graham]

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.