[Django] #36374: postgres ExclusionConstraint with multiple expressions breaks `create_model`

[Django]

#36374: postgres ExclusionConstraint with multiple expressions breaks
`create_model`
-------------------------------------+-------------------------------------
Reporter: anthony sottile | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | 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
-------------------------------------+-------------------------------------
the long and the short of this is `CREATE EXTENSION btree_gist;` needs to
be run at least once for this type of constraint to be possible -- but
django doesn't do this automatically for `create_model` when utilizing the
test database

in searching for related issues I found
https://code.djangoproject.com/ticket/33982 but that doesn't seem directly
tied to this problem

seems others have hit this as well without solution:
- https://stackoverflow.com/questions/23949024/using-postgresql-gin-or-
gist-index-with-bigint-column
- https://stackoverflow.com/questions/42329415/error-getting-when-
creating-gin-index-on-jsonb-column-postgresql9-5
- https://dba.stackexchange.com/questions/275946/postgres-create-
index#comment541407_275946
- https://stackoverflow.com/questions/45833855/prevent-daterangefield-
overlap-in-django-model#comment139392235_59912678

(I understand for migrations I need `BtreeGistExtension()` -- but that
isn't relevant here as I do not want to run migrations for general tests)

___

"minimal" reproduction

starting from `django-admin startproject mysite .`

- add `mysite` to `INSTALLED_APPS`

- add this to `mysite/settings.py` (or whatever port / user / password for
postgres):

{{{
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"USER": "postgres",
"NAME": "django",
"PASSWORD": "postgres",
"HOST": "localhost",
"PORT": 5432,
},
}
}}}

- add this `models.py` file:

{{{
from django.db import models
from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import DateTimeRangeField


class MyModel(models.Model):
subscription_id = models.BigIntegerField()
target_type = models.BigIntegerField()
period = DateTimeRangeField()

class Meta:
app_label = "mysite"
db_table = "my_model"
constraints = [
ExclusionConstraint(
name="accounts_spend_allocations_unique_per_period",
expressions=(
("subscription_id", "="),
("target_type", "="),
("period", "&&"),
),
)
]
}}}

- create `tests/test.py`:

{{{
from django.test import TestCase

class TestMyTest(TestCase):
def test(self):
pass
}}}

{{{
$ python manage.py test tests --noinput
Found 1 test(s).
Creating test database for alias 'default'...
Got an error creating the test database: database "test_django" already
exists

Destroying old test database for alias 'default'...
Traceback (most recent call last):
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/backends/utils.py", line 103, in _execute
return self.cursor.execute(sql)
~~~~~~~~~~~~~~~~~~~^^^^^
psycopg2.errors.UndefinedObject: data type bigint has no default operator
class for access method "gist"
HINT: You must specify an operator class for the index or define a
default operator class for the data type.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/private/tmp/y/manage.py", line 22, in <module>
main()
~~~~^^
File "/private/tmp/y/manage.py", line 18, in main
execute_from_command_line(sys.argv)
~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/__init__.py", line 442, in
execute_from_command_line
utility.execute()
~~~~~~~~~~~~~~~^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/__init__.py", line 436, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/commands/test.py", line 24, in
run_from_argv
super().run_from_argv(argv)
~~~~~~~~~~~~~~~~~~~~~^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/base.py", line 416, in run_from_argv
self.execute(*args, **cmd_options)
~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/base.py", line 460, in execute
output = self.handle(*args, **options)
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/commands/test.py", line 63, in handle
failures = test_runner.run_tests(test_labels)
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/test/runner.py", line 1092, in run_tests
old_config = self.setup_databases(
aliases=databases,
serialized_aliases=suite.serialized_aliases,
)
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/test/runner.py", line 990, in setup_databases
return _setup_databases(
self.verbosity,
...<5 lines>...
**kwargs,
)
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/test/utils.py", line 204, in setup_databases
connection.creation.create_test_db(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
verbosity=verbosity,
^^^^^^^^^^^^^^^^^^^^
...<2 lines>...
serialize=False,
^^^^^^^^^^^^^^^^
)
^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/backends/base/creation.py", line 78, in create_test_db
call_command(
~~~~~~~~~~~~^
"migrate",
^^^^^^^^^^
...<3 lines>...
run_syncdb=True,
^^^^^^^^^^^^^^^^
)
^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/__init__.py", line 194, in call_command
return command.execute(*args, **defaults)
~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/base.py", line 460, in execute
output = self.handle(*args, **options)
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/base.py", line 107, in wrapper
res = handle_func(*args, **kwargs)
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/commands/migrate.py", line 318, in handle
self.sync_apps(connection, executor.loader.unmigrated_apps)
~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/core/management/commands/migrate.py", line 480, in
sync_apps
editor.create_model(model)
~~~~~~~~~~~~~~~~~~~^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/backends/base/schema.py", line 512, in create_model
self.execute(sql, params or None)
~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/backends/postgresql/schema.py", line 45, in execute
return super().execute(sql, params)
~~~~~~~~~~~~~~~^^^^^^^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/backends/base/schema.py", line 204, in execute
cursor.execute(sql, params)
~~~~~~~~~~~~~~^^^^^^^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/backends/utils.py", line 79, in execute
return self._execute_with_wrappers(
~~~~~~~~~~~~~~~~~~~~~~~~~~~^
sql, params, many=False, executor=self._execute
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/backends/utils.py", line 100, in _execute
with self.db.wrap_database_errors:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/utils.py", line 91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/private/tmp/y/venv/lib/python3.13/site-
packages/django/db/backends/utils.py", line 103, in _execute
return self.cursor.execute(sql)
~~~~~~~~~~~~~~~~~~~^^^^^
django.db.utils.ProgrammingError: data type bigint has no default operator
class for access method "gist"
HINT: You must specify an operator class for the index or define a
default operator class for the data type.
}}}

(if I manually `--reusedb` and inject the `CREATE EXTENSION` command above
via `psql` then it continues as normal -- but that's a workaround "at
best")
--
Ticket URL: <https://code.djangoproject.com/ticket/36374>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36374: postgres ExclusionConstraint with multiple expressions breaks
`create_model`
-------------------------------------+-------------------------------------

Reporter: anthony sottile | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: duplicate

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

Changes (by Simon Charette):

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

Comment:

Duplicate of #35902, see ticket:35902#comment:1 for a workaround using
[https://docs.djangoproject.com/en/5.2/ref/settings/#template a test
database template].
--
Ticket URL: <https://code.djangoproject.com/ticket/36374#comment:1>

[Django]

#36374: postgres ExclusionConstraint with multiple expressions breaks
`create_model`
-------------------------------------+-------------------------------------
Reporter: anthony sottile | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by anthony sottile):

a template db doesn't really help -- *something* would need to set that up
and django has all the context and knowledge to do that for me but...
doesn't
--
Ticket URL: <https://code.djangoproject.com/ticket/36374#comment:2>

[Django]

#36374: postgres ExclusionConstraint with multiple expressions breaks
`create_model`
-------------------------------------+-------------------------------------
Reporter: anthony sottile | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by anthony sottile):

this seems more helpful (and is not as specific) but I think something
like this could live in the postgres schema editor:

{{{
class MakeBtreeGistSchemaEditor(PostgresDatabaseSchemaEditor):
"""workaround for https://code.djangoproject.com/ticket/36374"""
def create_model(self, model: type[Model]) -> None:
if any(isinstance(c, ExclusionConstraint) for c in
model._meta.constraints):
self.execute('CREATE EXTENSION IF NOT EXISTS btree_gist;')
super().create_model(model)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36374#comment:3>