[Django] #26167: Support for Functional Indexes (aka Indexes on Expressions)
Django
-------------------------------+--------------------
Reporter: cancan101 | Owner: nobody
Type: New feature | Status: new
Component: Uncategorized | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
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
--
Ticket URL: <https://code.djangoproject.com/ticket/26167>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: cancan101 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* needs_better_patch: => 0
* component: Uncategorized => Database layer (models, ORM)
* needs_tests: => 0
* version: 1.9 => master
* needs_docs: => 0
* stage: Unreviewed => Accepted
Comment:
I thought there was already a ticket for that given the existing
[https://github.com/django/deps/pull/6 DEP] but I couldn't find any.
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:1>
Django
Reporter: cancan101 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by akki):
* cc: aksheshdoshi@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:2>
Django
Reporter: cancan101 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* keywords: => db-indexes
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:3>
Django
Reporter: Alex Rothberg | Owner: Michael
| Blatherwick
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Michael Blatherwick):
* owner: nobody => Michael Blatherwick
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:4>
Django
Reporter: Alex Rothberg | Owner: Michael
| Blatherwick
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:5>
Django
Reporter: Alex Rothberg | Owner: (none)
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Michael Blatherwick):
* status: assigned => new
* owner: Michael Blatherwick => (none)
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:6>
Django
Reporter: Alex Rothberg | Owner: (none)
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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
{{{#!python
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:7>
Django
Reporter: Alex Rothberg | Owner: Markus
| Holtermann
Type: New feature | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Markus Holtermann):
* owner: (none) => Markus Holtermann
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:8>
Django
Reporter: Alex Rothberg | Owner: Markus
| Holtermann
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"19b2dfd1bfe7fd716dd3d8bfa5f972070d83b42f" 19b2dfd1]:
{{{
#!CommitTicketReference repository=""
revision="19b2dfd1bfe7fd716dd3d8bfa5f972070d83b42f"
Refs #11964, #26167 -- Made Expressions deconstructible.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:9>
Django
Reporter: Alex Rothberg | Owner: Markus
| Holtermann
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Markus Holtermann):
* has_patch: 0 => 1
Comment:
PR: https://github.com/django/django/pull/8056
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:10>
Django
Reporter: Alex Rothberg | Owner: Markus
| Holtermann
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Martin):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:11>
Django
Reporter: Alex Rothberg | Owner: Markus
| Holtermann
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Tim Graham <timograham@…>):
In [changeset:"bc7e288ca9554ac1a0a19941302dea19df1acd21" bc7e288]:
{{{
#!CommitTicketReference repository=""
revision="bc7e288ca9554ac1a0a19941302dea19df1acd21"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:12>
Django
Reporter: Alex Rothberg | Owner: Markus
| Holtermann
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by hannseman):
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:13>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Hannes Ljungberg):
* owner: Markus Holtermann => Hannes Ljungberg
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:14>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Hannes Ljungberg):
PR: https://github.com/django/django/pull/11929
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:15>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Hannes Ljungberg):
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:16>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:18>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Hannes Ljungberg):
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:19>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"d23cb83c99bc77c23e2fb5bff378d2f2092d7b29" d23cb83c]:
{{{
#!CommitTicketReference repository=""
revision="d23cb83c99bc77c23e2fb5bff378d2f2092d7b29"
Refs #26167 -- Made DatabaseSchemaEditor._create_index_sql()'s fields
argument optional and kwarg-only.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:20>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:21>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Component: Database layer | Version: master
(models, ORM) |
Keywords: db-indexes | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"83fcfc9ec8610540948815e127101f1206562ead" 83fcfc9e]:
{{{
#!CommitTicketReference repository=""
revision="83fcfc9ec8610540948815e127101f1206562ead"
Fixed #26167 -- Added support for functional indexes.
Thanks Simon Charette, Mads Jensen, and Mariusz Felisiak for reviews.
Co-authored-by: Markus Holtermann <in...@markusholtermann.eu>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:22>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: db-indexes | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"ffe756d6242d513bfbba9686b5a3d90f0a67c214" ffe756d6]:
{{{
#!CommitTicketReference repository=""
revision="ffe756d6242d513bfbba9686b5a3d90f0a67c214"
Refs #26167 -- Changed default value of
DatabaseFeatures.supports_expression_indexes to True.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:23>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: db-indexes | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"90ddf46ef7b3d775b124d81e1846bec7961c7f1f" 90ddf46e]:
{{{
#!CommitTicketReference repository=""
revision="90ddf46ef7b3d775b124d81e1846bec7961c7f1f"
Refs #26167 -- Corrected OpClass() example in docs.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:24>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: db-indexes | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"5d9374b9fb12843ab897355fd319fef898a95d01" 5d9374b9]:
{{{
#!CommitTicketReference repository=""
revision="5d9374b9fb12843ab897355fd319fef898a95d01"
[3.2.x] Refs #26167 -- Corrected OpClass() example in docs.
Backport of 90ddf46ef7b3d775b124d81e1846bec7961c7f1f from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:25>
Django
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: closed
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: db-indexes | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by GitHub <noreply@…>):
In [changeset:"76c0b32f826469320c59709d31e2f2126dd7c505" 76c0b32]:
{{{
#!CommitTicketReference repository=""
revision="76c0b32f826469320c59709d31e2f2126dd7c505"
Refs #26167 -- Added @skipUnlessDBFeature('supports_expression_indexes')
to a test.
Failure observed on CockroachDB.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:26>
Django
-------------------------------------+-------------------------------------
Reporter: Alex Rothberg | Owner: Hannes
| Ljungberg
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: db-indexes | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"cd4dc4c3f1ccc6664fca5c7c700fcf9e0830d74f" cd4dc4c]:
{{{
#!CommitTicketReference repository=""
revision="cd4dc4c3f1ccc6664fca5c7c700fcf9e0830d74f"
[3.2.x] Refs #26167 -- Added
@skipUnlessDBFeature('supports_expression_indexes') to a test.
Failure observed on CockroachDB.
Backport of 76c0b32f826469320c59709d31e2f2126dd7c505 from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26167#comment:27>