Altering spatial_index does not actually create/drop the index

[Mário Falcão]

Changing spatial_index on existing spatial fields causes migrations to be generated as expected, but they do not actually drop/create the indexes as desired when applied.

E.g. starting with the following model

class LocationUpdate(models.Model):
    location = PointField(geography=True, spatial_index=False)

If we change spatial_index to True, a migration is generated with a single operation:

migrations.AlterField(
    model_name="locationupdate",
    name="location",
    field=django.contrib.gis.db.models.fields.PointField(
        geography=True, srid=4326
    ),
)

But applying this migration does not create the expected index and the output of sqlmigrate is as follows:

BEGIN;
--
-- Alter field location on locationupdate
--
-- (no-op)
COMMIT;

Detected on Django 4.2.8 with PostGIS, but also present on latest main branch across all backends.

[David Smith]

Thanks for the report. However please don't accept your own tickets. I'll revert to unaccepted until someone else can review the report.

[Mário Falcão]

Got it. FYI I am working on a patch: ​https://github.com/django/django/pull/17662

It properly fixed this on PostGIS but the issue appears to also be present on MySQL as the tests I added fail there. I'll improve it and post an update here when it's ready.

[Mário Falcão]

Turns out that none of the GIS backends handled this and I believe they never did.

My patch is now ready for review - it should fix this on PostGIS, MySQL and Oracle. Spatialite is not implemented as it does not support altering geo fields.

[Mariusz Felisiak]

​PR (it is not the final fix, but something worth doing separately)

[Sarah Boyce <42296566+sarahboyce@…>]

In 20c2d62:

Refs #35074 -- Avoided failed attempts to remove spatial indexes on nullable fields on MySQL.

MySQL doesn't support spatial indexes on NULL columns, so there is no
point in removing them.

[Sarah Boyce <42296566+sarahboyce@…>]

In adb72fa8:

[5.1.x] Refs #35074 -- Avoided failed attempts to remove spatial indexes on nullable fields on MySQL.

MySQL doesn't support spatial indexes on NULL columns, so there is no
point in removing them.

Backport of 20c2d625d3d5062e43918d1d7b6f623202491dd4 from main.

[Mariusz Felisiak]

Jacob, there is still one related cleanup waiting for a review.

[Jacob Walls]

Thanks, the PR Mariusz mentions is: ​https://github.com/django/django/pull/18175

[Sarah Boyce <42296566+sarahboyce@…>]

In e3de574:

Refs #35074 -- Simplified and unified adding spatial indexes on MySQL and Oracle.

This uses deferred_sql and _field_indexes_sql() instead of custom
hooks on MySQL.

[Mariusz Felisiak]

​PR

[Sarah Boyce <42296566+sarahboyce@…>]

In d4bce26c:

Refs #35074 -- Added PostGISSchemaEditor._create_spatial_index_name().

This is consistent with Oracle and MySQL GIS database backends.

[Sarah Boyce <42296566+sarahboyce@…>]

In 371a9f3c:

Refs #35074 -- Added _create_spatial_index_sql()/_delete_spatial_index_sql() hooks to GIS backends.

[Sarah Boyce <42296566+sarahboyce@…>]

In f8cc9285:

Fixed #35074 -- Fixed adding/removing indexes when spatial_index is changed on MySQL, PostgreSQL, and Oracle.

Co-authored-by: Mário Falcão <mario@…>