Opened 8 months ago
Closed 6 months ago
#35275 closed Bug (fixed)
Constraints validation fails on UniqueConstraint using OpClass
Reported by: | Mathieu Kniewallner | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Gagaro | 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
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
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:
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.
Change History (6)
comment:1 by , 8 months ago
Cc: | added |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 8 months ago
Has patch: | set |
---|---|
Owner: | changed from | to
Status: | new → assigned |
comment:3 by , 8 months ago
Patch needs improvement: | set |
---|
comment:5 by , 6 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
Good catch, thanks for the report. Please prepare a patch via GitHub PR (a regression test is required).