Support for Functional Indexes (aka Indexes on Expressions)

[Alex Rothberg]

Add support for Support for Functional Indexes (aka Indexes on Expressions)

See: ​http://docs.sqlalchemy.org/en/latest/core/constraints.html#functional-indexes or ​http://www.postgresql.org/docs/current/static/indexes-expressional.html

[Simon Charette]

I thought there was already a ticket for that given the existing ​DEP but I couldn't find any.

[Michael Blatherwick]

I worked on this at the DUTH sprint but I was a long way from getting it working and I won't be able to take it any further. I don't know how much of my approach was even correct, but my branch is at ​https://github.com/exonian/django/tree/functional-indexes anyway in case
parts of it can be informative to others.

Ian's PR ​https://github.com/django/django/pull/7504 makes Expressions deconstructible, which this relies on.

[Markus Holtermann]

Here's my shot at expression based functional indexes: ​https://github.com/django/django/pull/8056

There's a whole lot more to do. Random things that just come to mind:

• Field validation -- validate that the fields in the expression exist on the model
• Look into getting rid of Ref() and output_field as much as possible
• Work out what "functions in index expression must be marked IMMUTABLE" means on PostgreSQL -- Running the example form the PR description on PostgreSQL results in

  Applying app.0001_initial...Traceback (most recent call last):
    File "/home/markus/Coding/django/django/db/backends/utils.py", line 62, in execute
      return self.cursor.execute(sql, params)
  psycopg2.ProgrammingError: functions in index expression must be marked IMMUTABLE
  
  
  The above exception was the direct cause of the following exception:
  
  Traceback (most recent call last):
    File "manage.py", line 10, in <module>
      execute_from_command_line(sys.argv)
    File "/home/markus/Coding/django/django/core/management/__init__.py", line 361, in execute_from_command_line
      utility.execute()
    File "/home/markus/Coding/django/django/core/management/__init__.py", line 353, in execute
      self.fetch_command(subcommand).run_from_argv(self.argv)
    File "/home/markus/Coding/django/django/core/management/base.py", line 280, in run_from_argv
      self.execute(*args, **cmd_options)
    File "/home/markus/Coding/django/django/core/management/base.py", line 327, in execute
      output = self.handle(*args, **options)
    File "/home/markus/Coding/django/django/core/management/commands/migrate.py", line 201, in handle
      fake_initial=fake_initial,
    File "/home/markus/Coding/django/django/db/migrations/executor.py", line 113, in migrate
      state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
    File "/home/markus/Coding/django/django/db/migrations/executor.py", line 143, in _migrate_all_forwards
      state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
    File "/home/markus/Coding/django/django/db/migrations/executor.py", line 242, in apply_migration
      state = migration.apply(state, schema_editor)
    File "/home/markus/Coding/django/django/db/migrations/migration.py", line 122, in apply
      operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
    File "/home/markus/Coding/django/django/db/migrations/operations/models.py", line 782, in database_forwards
      schema_editor.add_index(model, self.index)
    File "/home/markus/Coding/django/django/db/backends/base/schema.py", line 330, in add_index
      self.execute(index.create_sql(model, self))
    File "/home/markus/Coding/django/django/db/backends/base/schema.py", line 119, in execute
      cursor.execute(sql, params)
    File "/home/markus/Coding/django/django/db/backends/utils.py", line 77, in execute
      return super().execute(sql, params)
    File "/home/markus/Coding/django/django/db/backends/utils.py", line 62, in execute
      return self.cursor.execute(sql, params)
    File "/home/markus/Coding/django/django/db/utils.py", line 90, in __exit__
      raise dj_exc_value.with_traceback(traceback)
    File "/home/markus/Coding/django/django/db/backends/utils.py", line 62, in execute
      return self.cursor.execute(sql, params)
  django.db.utils.ProgrammingError: functions in index expression must be marked IMMUTABLE
  

The SQL that raises the exception is CREATE INDEX "some_func_index" ON "app_foo" (CONCAT(LOWER("name"), 'blub'))

[Tim Graham <timograham@…>]

In 19b2dfd1:

Refs #11964, #26167 -- Made Expressions deconstructible.

[Markus Holtermann]

PR: ​https://github.com/django/django/pull/8056

[Tim Graham <timograham@…>]

In bc7e288:

Fixed #29745 -- Based Expression equality on detailed initialization signature.

The old implementation considered objects initialized with an equivalent
signature different if some arguments were provided positionally instead of
as keyword arguments.

Refs #11964, #26167.

[Hannes Ljungberg]

It have been a good while since there was any activity on this issue, both here and on the GitHub PR. By reading the PR comments there’s still a community interest in getting this feature into Django. Me personally would love to use it to set B-Tree Indexes on keys in JSONField s by using KeyTransform.

A lot of stuff have happened in regards to index creation since the last work was done on the PR. I have a proof-of-concept based on the latest master using a lot of work done by Markus Holtermann.

@Markus Holtermann would you mind if I opened a new PR to continue your work?

[Hannes Ljungberg]

PR: ​https://github.com/django/django/pull/11929

[Mariusz Felisiak <felisiak.mariusz@…>]

In d23cb83c:

Refs #26167 -- Made DatabaseSchemaEditor._create_index_sql()'s fields argument optional and kwarg-only.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 83fcfc9e:

Fixed #26167 -- Added support for functional indexes.

Thanks Simon Charette, Mads Jensen, and Mariusz Felisiak for reviews.

Co-authored-by: Markus Holtermann <info@…>

[Mariusz Felisiak <felisiak.mariusz@…>]

In ffe756d6:

Refs #26167 -- Changed default value of DatabaseFeatures.supports_expression_indexes to True.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 90ddf46e:

Refs #26167 -- Corrected OpClass() example in docs.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 5d9374b9:

[3.2.x] Refs #26167 -- Corrected OpClass() example in docs.

Backport of 90ddf46ef7b3d775b124d81e1846bec7961c7f1f from master

[GitHub <noreply@…>]

In 76c0b32:

Refs #26167 -- Added @skipUnlessDBFeature('supports_expression_indexes') to a test.

Failure observed on CockroachDB.

[Mariusz Felisiak <felisiak.mariusz@…>]

In cd4dc4c:

[3.2.x] Refs #26167 -- Added @skipUnlessDBFeature('supports_expression_indexes') to a test.

Failure observed on CockroachDB.
Backport of 76c0b32f826469320c59709d31e2f2126dd7c505 from main