#35275: Constraints validation fails on UniqueConstraint using OpClass
-------------------------------------+-------------------------------------
Reporter: Mathieu | Owner: nobody
Kniewallner |
Type: Bug | Status: new
Component: Database | Version: dev
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Adding a `UniqueConstraint` using PostgreSQL-specific `OpClass` on a model
breaks constraints validation performed when calling
`validate_constraints` on a model when using PostgreSQL.
=== Minimal reproducer
{{{#!python
from django.contrib.postgres.indexes import OpClass
from django.db import models
from django.db.models.functions import Lower
class Place(models.Model):
name = models.CharField(max_length=255)
class Meta:
app_label = "opclass_issue"
constraints = [
models.UniqueConstraint(
OpClass(Lower("name"), name="text_pattern_ops"),
name="lower_name_uniq",
)
]
place = Place(name="Narnia")
place.validate_constraints()
}}}
This leads to the following error:
{{{
django.db.utils.ProgrammingError: syntax error at or near
"text_pattern_ops"
LINE 1: ...place" WHERE LOWER("opclass_issue_place"."name") text_patte...
}}}
Full SQL query that is generated:
{{{#!sql
SELECT 1 AS "a" FROM "opclass_issue_place" WHERE
LOWER("opclass_issue_place"."name") text_pattern_ops = (LOWER('narnia')
text_pattern_ops) LIMIT 1
}}}
Just in case, this happens even though `django.contrib.postgres` is
correctly installed in `INSTALLED_APPS`, so the issue is not related to
that.
I've also created a test and adapted the CI to run it in
https://github.com/backmarket-oss/django/pull/2/files, which leads to
https://github.com/backmarket-
oss/django/actions/runs/8171237603/job/22339033423?pr=2 showing the
failure.
=== Potential root cause
`OpClass` wrapper should only make sense when creating the constraint, and
should not be used when validating the constraint, as this leads to an
invalid SQL query.
Looking at the code for the PostgreSQL specific `ExclusionConstraint`, a
similar conclusion was reached, and `OpClass` is explicitly removed in
`validate` method:
https://github.com/django/django/blob/4426b1a72dc289643e2ae8c190b8dc4b3a39daf7/django/contrib/postgres/constraints.py#L211-L216.
=== Potential fix
Applying a similar fix as the one above in `UniqueConstraint`, showcased
in https://github.com/backmarket-oss/django/pull/1/files applied on top of
the other PR above, leads to https://github.com/backmarket-
oss/django/actions/runs/8171242801/job/22339050847?pr=1 where the added
test is now passing.
If you think that this is the correct fix to apply, or have a lead for
another fix, I'd be happy to make a proper pull request targeting Django
repository.
--
Ticket URL: <https://code.djangoproject.com/ticket/35275>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018e13ca0979-fa582a9e-c0cb-4892-9842-97c8207da856-000000%40eu-central-1.amazonses.com.
