Opened 2 years ago

Last modified 6 months ago

#26167 assigned New feature

Support for Functional Indexes (aka Indexes on Expressions)

Reported by: Alex Rothberg Owned by: Markus Holtermann
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: db-indexes
Cc: aksheshdoshi@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Change History (11)

comment:1 Changed 2 years ago by Simon Charette

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 Changed 2 years ago by Akshesh Doshi

Cc: aksheshdoshi@… added

comment:3 Changed 2 years ago by Tim Graham

Keywords: db-indexes added

comment:4 Changed 20 months ago by Michael Blatherwick

Owner: changed from nobody to Michael Blatherwick
Status: newassigned

comment:5 Changed 20 months ago by 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.

comment:6 Changed 20 months ago by Michael Blatherwick

Owner: Michael Blatherwick deleted
Status: assignednew

comment:7 Changed 16 months ago by 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'))

Last edited 16 months ago by Markus Holtermann (previous) (diff)

comment:8 Changed 16 months ago by Markus Holtermann

Owner: set to Markus Holtermann
Status: newassigned

comment:9 Changed 16 months ago by Tim Graham <timograham@…>

In 19b2dfd1:

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

comment:10 Changed 11 months ago by Markus Holtermann

Has patch: set

comment:11 Changed 6 months ago by Tim Martin

Patch needs improvement: set
Note: See TracTickets for help on using tickets.
Back to Top