Opened 8 years ago

Closed 3 years ago

Last modified 3 years ago

#26167 closed New feature (fixed)

Support for Functional Indexes (aka Indexes on Expressions)

Reported by: Alex Rothberg Owned by: Hannes Ljungberg
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: db-indexes
Cc: aksheshdoshi@…, Ian Foote 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

Change History (27)

comment:1 by Simon Charette, 8 years ago

Component: UncategorizedDatabase layer (models, ORM)
Triage Stage: UnreviewedAccepted
Version: 1.9master

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

comment:2 by Akshesh Doshi, 8 years ago

Cc: aksheshdoshi@… added

comment:3 by Tim Graham, 8 years ago

Keywords: db-indexes added

comment:4 by Michael Blatherwick, 7 years ago

Owner: changed from nobody to Michael Blatherwick
Status: newassigned

comment:5 by Michael Blatherwick, 7 years ago

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.

comment:6 by Michael Blatherwick, 7 years ago

Owner: Michael Blatherwick removed
Status: assignednew

comment:7 by Markus Holtermann, 7 years ago

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'))

Last edited 7 years ago by Markus Holtermann (previous) (diff)

comment:8 by Markus Holtermann, 7 years ago

Owner: set to Markus Holtermann
Status: newassigned

comment:9 by Tim Graham <timograham@…>, 7 years ago

In 19b2dfd1:

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

comment:10 by Markus Holtermann, 7 years ago

Has patch: set

comment:11 by Tim Martin, 6 years ago

Patch needs improvement: set

comment:12 by Tim Graham <timograham@…>, 5 years ago

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.

comment:13 by Hannes Ljungberg, 4 years ago

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?

comment:14 by Hannes Ljungberg, 4 years ago

Owner: changed from Markus Holtermann to Hannes Ljungberg

comment:16 by Hannes Ljungberg, 4 years ago

Patch needs improvement: unset

comment:17 by Ian Foote, 4 years ago

Cc: Ian Foote added

comment:18 by Mariusz Felisiak, 4 years ago

Patch needs improvement: set

comment:19 by Hannes Ljungberg, 3 years ago

Patch needs improvement: unset

comment:20 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In d23cb83c:

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

comment:21 by Mariusz Felisiak, 3 years ago

Triage Stage: AcceptedReady for checkin

comment:22 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

Resolution: fixed
Status: assignedclosed

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

comment:23 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In ffe756d6:

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

comment:24 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In 90ddf46e:

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

comment:25 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In 5d9374b9:

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

Backport of 90ddf46ef7b3d775b124d81e1846bec7961c7f1f from master

comment:26 by GitHub <noreply@…>, 3 years ago

In 76c0b32:

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

Failure observed on CockroachDB.

comment:27 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In cd4dc4c:

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

Failure observed on CockroachDB.
Backport of 76c0b32f826469320c59709d31e2f2126dd7c505 from main

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