#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.
--
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 [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070196ac747674-d63428c7-6fc2-4bc0-adad-d04295f6e101-000000%40eu-central-1.amazonses.com.
