#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
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
Change History (27)
comment:1 by , 9 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Triage Stage: | Unreviewed → Accepted |
Version: | 1.9 → master |
comment:2 by , 9 years ago
Cc: | added |
---|
comment:3 by , 8 years ago
Keywords: | db-indexes added |
---|
comment:4 by , 8 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:5 by , 8 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 , 8 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
comment:7 by , 8 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()
andoutput_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'))
comment:8 by , 8 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:11 by , 7 years ago
Patch needs improvement: | set |
---|
comment:13 by , 5 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 , 5 years ago
Owner: | changed from | to
---|
comment:16 by , 5 years ago
Patch needs improvement: | unset |
---|
comment:17 by , 4 years ago
Cc: | added |
---|
comment:18 by , 4 years ago
Patch needs improvement: | set |
---|
comment:19 by , 4 years ago
Patch needs improvement: | unset |
---|
comment:21 by , 4 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
I thought there was already a ticket for that given the existing DEP but I couldn't find any.