Don't issue `CREATE INDEX` on initial migration

Django Internals ORM
1

Hi, I’m developing a driver for CrateDB.

It does not support adding a new index after table creation (everything is already indexed by default)

Running initial migrations sessions.0001_initial tries to run
CREATE INDEX "django_session_expire_date_a5c62663" ON "django_session" ("expire_date")

How do I stop django from issuing that statement?

I have tried setting:

class DatabaseFeatures(BaseDatabaseFeatures):
    # Does the backend support partial indexes (CREATE INDEX ... WHERE ...)?
    supports_partial_indexes = False
    supports_functions_in_partial_indexes = False

    # Does the backend support indexes on expressions?
    supports_expression_indexes = False

class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):

    def add_index(self, model, index):
        return ()

    def add_constraint(self, model, constraint):
        return ()

But it still issues it, a ‘hack’ would be to set sql_create_index = ("select 1") but I’d like to push the right button.

Thanks in advance.

2 
    def add_index(self, model, index):
        """Add an index on a model."""
        if (
            index.contains_expressions
            and not self.connection.features.supports_expression_indexes
        ):
            return None
        # Index.create_sql returns interpolated SQL which makes params=None a
        # necessity to avoid escaping attempts on execution.
        self.execute(index.create_sql(model, self), params=None)

^ this is on BaseDatabaseSchemaEditor, I think you could, in your backend, jsut override that to return None all the time? Would want to do similarly for remove_index and rename_index.

It might make sense to add this as a feature if other backends show up with these constraints, but for now having your backend-specific SchemaEditor no-op on the index creation and removal seems the most straightforward.

If you’re having issues with your overrides, could you post some stack traces? I think that returning None should “just work” (and if it doesn’t, the issue might affect our expression index support flag as well)

3

Hi, it does not work by just returning None.

Looking at django/db/migrations/operations/models.py, schema_editor.add_index only gets called in AddIndex and RemoveIndex operations, in this situation we are creating a new table, therefore CreateModel is the one being called, and consequentially create_model

In django/db/backends/base/schema.BaseDatabaseSchemaEditor.create_model we have this line

def create_model(self, model):
    ...
    self.deferred_sql.extend(self._model_indexes_sql(model))
    ...

If we look at _model_indexes_sql

    def _model_indexes_sql(self, model):
        """
        Return a list of all index SQL statements (field indexes, Meta.indexes)
        for the specified model.
        """
        if not model._meta.managed or model._meta.proxy or model._meta.swapped:
            return []
        output = []
        for field in model._meta.local_fields:
            output.extend(self._field_indexes_sql(model, field))

        for index in model._meta.indexes:
            if (
                not index.contains_expressions
                or self.connection.features.supports_expression_indexes
            ):
                output.append(index.create_sql(model, self))
        return output

we see that features.supports_expression_indexes is only checked on meta.indexes, not on local_fields (fields with db_index = True and not field.unique), that’s why regardless of what I set, it still issues the create statement.

I would consider this a bug, if my code search is not failing, _model_indexes_sql is only used for deferred index creation, it should imo be safe and correct to fast return [ ] if not self.connection.features.supports_expression_indexes

5

Thanks for the breakdown, I understand what is going on now.

I … feel like we could rework the code in _model_indexes_sql to to use add_index but would need to poke around a bit to understand the consequences of that. If I had to guess that code is older than add_index is.

Just to clarify, is the idea here that CREATE INDEX doesn’t work for this database at all? Or is it just that indices are implicitly created at table creation time?

If they don’t work at all then it does feel like the SELECT 1 hack would be apt. If it’s only about model creation, I think we should “fix” _model_indexes_sql.

6

For my specific use case, its both: CrateDB does not support creating or deleting an index after table creation, so the syntax does not exist in the SQL dialect. Also, all fields are indexed by default (implicitly) following a Hybrid approach (it might create two indexes with different data structures for every field)

7

If the DB doesn’t support creating or deleting an index at all, the “hack” of changing the create index template feels alright.

There’s an easy change we could make here, though, which would be for BaseDatabaseSchemaEditor to provide an override point for create_model’s index creation step in particular. Feels basically free, and would allow you to set up what you need here for your backend.

I feel like we would need other override points though… as an implementer what would be the ideal sort of interface that you would expecte? I do want to lean more towards “customize handling of indexes” rather than “turn indexes on and off” (since turning indexes off feels like an edge case).

8

Yeah, for now I’m just doing:

class DatabasechemaEditor(BaseDatabaseSchemaEditor):
    def _model_indexes_sql(self, model):
        """
        todo pgdiff

        This overload stops django from issuing CREATE INDEX statements.
        https://forum.djangoproject.com/t/dont-issue-create-index-on-initial-migration/36227/4
        """
        return []

The way I see it, there is already a mechanism to customize the behavior of the orm, BaseDatabaseWrapper.feature_class, I would expect for supports_expression_indexes to work properly or even maybe, rename that one or create something like supports_deffered_index_creation, since it’s name if a bit ambiguous for me, I’d even argue it’s wrong.

I’m fine having to override several pieces; I do not like sending SELECT 1s nor overriding private methods.