Constraints validation fails on UniqueConstraint using OpClass

[Mathieu Kniewallner]

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.

[Mariusz Felisiak]

Good catch, thanks for the report. Please prepare a patch via GitHub PR (a regression test is required).