[Django] #35136: Interoperability with intarray PostgreSQL extension

[Django]

#35136: Interoperability with intarray PostgreSQL extension
-------------------------------------+-------------------------------------
Reporter: Jan Hamal | Owner: (none)
Dvořák |
Type: New | Status: new
feature |
Component: | Version: dev
contrib.postgres | Keywords: postgresql,
Severity: Normal | intarray
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
`django.contrib.postgres.lookups` makes use of bare `&&`, `<@` and `@>`
operators. The `intarray` extension defines it's own set of these
operators specific to `int4[]`. When using `__overlap` and such on a
column with `int2[]`, PostgreSQL does not know what operator to use
(cannot decide between casting to int4[] and using the generic version)
and fails with "operator is not unique" error.

This ambiguity could be resolved by using `OPERATOR(pg_catalog.&&)` and
such instead of bare `&&` within the `django.contrib.postgres.lookups`
module.

We have locally worked around the issue by changing the operators upon
startup, but we are not very comfortable with live-patching Django classes
as a long-term solution.

--
Ticket URL: <https://code.djangoproject.com/ticket/35136>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35136: Interoperability with intarray PostgreSQL extension
-------------------------------------+-------------------------------------

Reporter: Jan Hamal Dvořák | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage:
intarray | Unreviewed
Has patch: 1 | Needs documentation: 0

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

Changes (by Jan Hamal Dvořák):

* Attachment "intarray-compat.patch" added.

Proposed change

[Django]

#35136: Interoperability with intarray PostgreSQL extension
-------------------------------------+-------------------------------------
Reporter: Jan Hamal Dvořák | Owner: (none)

Type: New feature | Status: closed
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution: wontfix

Keywords: postgresql, | Triage Stage:
intarray | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* status: new => closed
* resolution: => wontfix

Comment:

Thank you for your report.

I don't think that Django can commit to producing SQL that is compatible
with all the combination of usage of Postgres extensions out there.

Since it allows for [https://docs.djangoproject.com/en/5.0/howto/custom-
lookups/ any lookups to be overridden] it should be trivial to write your
own subclass of `ArrayContains` and friends and register them on
`ArrayField`.

{{{#!python
from django.contrib.postgres.fields.array import ArrayContains

class ArrayContainsOperator(ArrayContains):
postgres_operator = "OPERATOR(pg_catalog.@>)"

ArrayField.register_lookup(ArrayField.register_lookup)
}}}

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

[Django]

#35136: Interoperability with intarray PostgreSQL extension
-------------------------------------+-------------------------------------
Reporter: Jan Hamal Dvořák | Owner: (none)
Type: New feature | Status: closed
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution: wontfix
Keywords: postgresql, | Triage Stage:
intarray | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jan Hamal Dvořák):

Thank you for a timely reply.

> I don't think that Django can commit to producing SQL that is compatible
with all the combination of usage of Postgres extensions out there.

I feel this is somewhat unfair. I am not asking for perfect support of
every possible extension out there. Django already supports multiple
PostgreSQL extensions. The `intarray` is another one of the handful of
extensions bundled with PostgreSQL and available out of the box on
mainstream Linux distributions. It doesn't feel like a stretch to expect
Django won't break if I enable it, but I won't press you.

Thanks for a cleaner workaround.
--
Ticket URL: <https://code.djangoproject.com/ticket/35136#comment:2>