[Django] #36827: Adding hash index support for exclusion constraint in PostgreSQL

[Django]

#36827: Adding hash index support for exclusion constraint in PostgreSQL
----------------------+--------------------------------------------
Reporter: haki | Type: New feature
Status: new | Component: contrib.postgres
Version: 6.0 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------+--------------------------------------------
In PostgreSQL you can’t define a unique constraint using a hash index.
However, you can enforce uniqueness using an exclusion constraint and a
hash index. This is useful for large values that only use equality and
that are not referenced by FKs (fields like UIDs, hashes, checksums, URLs,
other large texts etc.).

However, the current
implementation(https://github.com/django/django/blob/4426b1a72dc289643e2ae8c190b8dc4b3a39daf7/django/contrib/postgres/constraints.py#L38)
of `ExclusionConstraint` in `django.contrib.postgres.constraints` is
limited to gist and spgist. It seems like the implementation haven’t
changed much since it was added in 2019.

I made a local change to allow hash to the list of allowed `index_type`s
and generated this constraint:

{{{
from django.contrib.postgres.constraints import ExclusionConstraint

class ShortUrl(models.Model):
class Meta:
constraints = (
ExclusionConstraint(
index_type='hash', # <--- this index type is currently
unsupported
expressions=[
(F('url'), '='),
],
name='%(app_label)s_url_unique_hash',
),
)
}}}

It produced the expected SQL:



{{{
ALTER TABLE "shorturl_shorturl" ADD CONSTRAINT "shorturl_url_unique_hash"
EXCLUDE USING hash ("url" WITH =);
}}}


After applying the migration the constraint worked as expected.
--
Ticket URL: <https://code.djangoproject.com/ticket/36827>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36827: Adding hash index support for exclusion constraint in PostgreSQL

----------------------------------+--------------------------------------
Reporter: haki | Owner: (none)

Type: New feature | Status: new
Component: contrib.postgres | Version: 6.0

Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

----------------------------------+--------------------------------------
Changes (by haki):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36827#comment:1>

[Django]

#36827: Adding hash index support for exclusion constraint in PostgreSQL
----------------------------------+--------------------------------------
Reporter: haki | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: 6.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------

Comment (by haki):

PR link [https://github.com/django/django/pull/20462]
--
Ticket URL: <https://code.djangoproject.com/ticket/36827#comment:2>

[Django]

#36827: Adding hash index support for exclusion constraint in PostgreSQL
----------------------------------+--------------------------------------

Reporter: haki | Owner: haki
Type: New feature | Status: assigned

Component: contrib.postgres | Version: 6.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------

Changes (by JaeHyuckSa):

* owner: (none) => haki
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36827#comment:3>

[Django]

#36827: Adding hash index support for exclusion constraint in PostgreSQL
----------------------------------+------------------------------------

Reporter: haki | Owner: haki
Type: New feature | Status: assigned
Component: contrib.postgres | Version: 6.0
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+------------------------------------

Changes (by Lily):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/36827#comment:4>

[Django]

#36827: Adding hash index support for exclusion constraint in PostgreSQL
----------------------------------+------------------------------------
Reporter: haki | Owner: haki
Type: New feature | Status: assigned
Component: contrib.postgres | Version: 6.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
----------------------------------+------------------------------------

Changes (by Simon Charette):

* needs_better_patch: 0 => 1

Comment:

Patch is looking great except for the lack of graceful handling of
covering indices which will require adjustments and additional tests.
--
Ticket URL: <https://code.djangoproject.com/ticket/36827#comment:5>

[Django]

#36827: Adding hash index support for exclusion constraint in PostgreSQL

-------------------------------------+-------------------------------------

Reporter: haki | Owner: haki
Type: New feature | Status: assigned
Component: contrib.postgres | Version: 6.0
Severity: Normal | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36827#comment:6>

[Django]

#36827: Adding hash index support for exclusion constraint in PostgreSQL
-------------------------------------+-------------------------------------
Reporter: haki | Owner: haki

Type: New feature | Status: closed
Component: contrib.postgres | Version: 6.0
Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"d61838761f17d7c934c7be288fadfa14f471b598" d618387]:
{{{#!CommitTicketReference repository=""
revision="d61838761f17d7c934c7be288fadfa14f471b598"
Fixed #36827 -- Added support for exclusion constraints using Hash indexes
on PostgreSQL.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36827#comment:7>