Geometry Index with PostGIS using schema

[Alan D. Snow]

If you have a model like this:

from django.contrib.gis.db import models as gis_models
from django.db import models

class MyModel(models.Model):
    geometry = gis_models.GeometryField(
        help_text="The area of interest.",
        spatial_index=True,
        srid=4326,
    )
    class Meta:
        db_table = 'source"."mymodel'

When you go to run migrations, this error occurs:

self = <django.db.backends.utils.CursorWrapper object at 0x7f3001dcadc0>
sql = 'CREATE INDEX "source"."mymodel_geometry_id" ON "source"."mymodel" USING GIST ("geometry")'
params = ()
ignored_wrapper_args = (False, {'connection': <django.contrib.gis.db.backends.postgis.base.DatabaseWrapper object at 0x7f3002ab6790>, 'cursor': <django.db.backends.utils.CursorWrapper object at 0x7f3001dcadc0>})

    def _execute(self, sql, params, *ignored_wrapper_args):
        self.db.validate_no_broken_transaction()
        with self.db.wrap_database_errors:
            if params is None:
                # params default might be backend specific.
                return self.cursor.execute(sql)
            else:
>               return self.cursor.execute(sql, params)
E               psycopg2.errors.SyntaxError: syntax error at or near "."
E               LINE 1: CREATE INDEX "source"."mymodel_geometry_id" ON "sour...
E                                            ^

Currently, the code that generates the name is here:
​https://github.com/django/django/blob/65a9d0013d202447dd76a9cb3c939aa5c9d23da3/django/contrib/gis/db/backends/postgis/schema.py#L35-L38

        if kwargs.get('name') is None:
            index_name = '%s_%s_id' % (model._meta.db_table, field.column)
        else:
            index_name = kwargs['name']

This produces the index name (as seen in the error above):
source"."mymodel_geometry_id

We have been patching this internally with this code instead:

        name = kwargs.get("name")
        if name is None:
            name = self._create_index_name(
                model._meta.db_table, [field.column], kwargs.get("suffix", "")
            )

This makes the name something like:
mymodel_geometry_26ff3048

Is this a patch you would like to see? Or do you have a different recommended solution?

[Mariusz Felisiak]

Thanks it looks valid, we should use the _create_index_name() hook instead, e.g. self._create_index_name(model._meta.db_table, [field.column], suffix='_id'). Would you like to prepare a patch? (tests are required).

[Alan D. Snow]

Happy to submit a patch. Ref: ​https://github.com/django/django/pull/14364

[Mariusz Felisiak]

​New PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 99bc67a:

Refs #32721 -- Made PostGISSchemaEditor._create_index_sql() call super()._create_index_sql().

[Mariusz Felisiak <felisiak.mariusz@…>]

In 29345aec:

Fixed #32721 -- Fixed migrations crash when adding namespaced spatial indexes on PostGIS.

[Tim Graham]

The first commit here makes the GIST_GEOMETRY_OPS_ND opclass disappear from the creation of PostGIS's 3D geometry indexes (#33294).