Opened 11 months ago

Closed 2 months ago

#35074 closed Bug (fixed)

Altering spatial_index does not actually create/drop the index

Reported by: Mário Falcão Owned by: Mariusz Felisiak
Component: GIS Version: 4.2
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (22)

comment:1 by Mário Falcão, 11 months ago

Owner: changed from nobody to Mário Falcão
Status: newassigned

comment:2 by Mário Falcão, 11 months ago

Triage Stage: UnreviewedAccepted

comment:3 by David Smith, 11 months ago

Triage Stage: AcceptedUnreviewed

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

comment:4 by Mário Falcão, 11 months ago

Has patch: set
Patch needs improvement: set

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.

comment:5 by Mário Falcão, 11 months ago

Description: modified (diff)
Patch needs improvement: unset

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.

comment:6 by Tim Graham, 11 months ago

Triage Stage: UnreviewedAccepted

comment:7 by Mariusz Felisiak, 10 months ago

Patch needs improvement: set

comment:8 by Mariusz Felisiak, 5 months ago

Owner: changed from Mário Falcão to Mariusz Felisiak
Patch needs improvement: unset

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

comment:9 by Sarah Boyce <42296566+sarahboyce@…>, 5 months ago

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.

comment:10 by Sarah Boyce <42296566+sarahboyce@…>, 5 months ago

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.

comment:11 by Jacob Walls, 5 months ago

Owner: Mariusz Felisiak removed
Patch needs improvement: set
Status: assignednew

in reply to:  11 comment:12 by Mariusz Felisiak, 5 months ago

Owner: set to Mariusz Felisiak
Patch needs improvement: unset
Status: newassigned

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

comment:13 by Jacob Walls, 5 months ago

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

comment:14 by Sarah Boyce <42296566+sarahboyce@…>, 4 months ago

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.

comment:15 by Sarah Boyce, 4 months ago

Has patch: unset

comment:16 by Mariusz Felisiak, 3 months ago

Has patch: set

comment:17 by Sarah Boyce <42296566+sarahboyce@…>, 2 months ago

In d4bce26c:

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

This is consistent with Oracle and MySQL GIS database backends.

comment:18 by Sarah Boyce <42296566+sarahboyce@…>, 2 months ago

In 371a9f3c:

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

comment:19 by Sarah Boyce, 2 months ago

Needs tests: set

comment:20 by Mariusz Felisiak, 2 months ago

Needs tests: unset

comment:21 by Sarah Boyce, 2 months ago

Triage Stage: AcceptedReady for checkin

comment:22 by Sarah Boyce <42296566+sarahboyce@…>, 2 months ago

Resolution: fixed
Status: assignedclosed

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@…>

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