#35194: Postgres 16.2 with _iexact leads to
psycopg2.errors.IndeterminateCollation
----------------------------------+--------------------------------------
Reporter: Aldalen | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 5.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------
Description changed by Aldalen:
Old description:
> There is something up with how "_iexact" GeneratedField expressions are
> handling _iexact expressions. This leads to a
> "psycopg2.errors.IndeterminateCollation: could not determine which
> collation to use for upper() function" error. This was caught in our
> CI/CD env when I revved it to 16.2. Existing / old migrations failed.
> This is a reduced examples from our code, with exception paths redacted
> where necessary.
>
> Model Definition:
>
> {{{
> class TestModel(models.Model):
> test = models.CharField(max_length=254, blank=True)
> test_gen =
> models.GeneratedField(expression=models.Q(test__iexact='yes'),
> output_field=models.BooleanField(), db_persist=True)
> }}}
>
> Migration content:
>
> {{{
> migrations.AddField(
> model_name='testmodel',
> name='test_gen',
> field=models.GeneratedField(db_persist=True,
> expression=models.Q(("test__iexact",'yes')),
> output_field=models.BooleanField()),
> )
> }}}
>
> Error:
>
> {{{
> Applying module.migration_name...Traceback (most recent call last):
> File "/opt/venv/lib/python3.11/site-
> packages/django/db/backends/utils.py", line 103, in _execute
> return self.cursor.execute(sql)
> ^^^^^^^^^^^^^^^^^^^^^^^^
> psycopg2.errors.IndeterminateCollation: could not determine which
> collation to use for upper() function
> HINT: Use the COLLATE clause to set the collation explicitly.
> The above exception was the direct cause of the following exception:
> Traceback (most recent call last):
> File "/builds/[redacted]/manage.py", line 15, in <module>
> execute_from_command_line(sys.argv)
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/core/management/__init__.py", line 442, in
> execute_from_command_line
> utility.execute()
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/core/management/__init__.py", line 436, in execute
> self.fetch_command(subcommand).run_from_argv(self.argv)
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/core/management/base.py", line 413, in run_from_argv
> self.execute(*args, **cmd_options)
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/core/management/base.py", line 459, in execute
> output = self.handle(*args, **options)
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/core/management/base.py", line 107, in wrapper
> res = handle_func(*args, **kwargs)
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/core/management/commands/migrate.py", line 356, in handle
> post_migrate_state = executor.migrate(
> ^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/migrations/executor.py", line 135, in migrate
> state = self._migrate_all_forwards(
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/migrations/executor.py", line 167, in
> _migrate_all_forwards
> state = self.apply_migration(
> ^^^^^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/migrations/executor.py", line 252, in apply_migration
> state = migration.apply(state, schema_editor)
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/migrations/migration.py", line 132, in apply
> operation.database_forwards(
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/migrations/operations/fields.py", line 108, in
> database_forwards
> schema_editor.add_field(
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/backends/base/schema.py", line 750, in add_field
> self.execute(sql, params)
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/backends/postgresql/schema.py", line 48, in execute
> return super().execute(sql, None)
> ^^^^^^^^^^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/backends/base/schema.py", line 201, in execute
> cursor.execute(sql, params)
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/backends/utils.py", line 122, in execute
> return super().execute(sql, params)
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/backends/utils.py", line 79, in execute
> return self._execute_with_wrappers(
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
> return executor(sql, params, many, context)
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/backends/utils.py", line 100, in _execute
> with self.db.wrap_database_errors:
> File "/opt/[redacted]/lib/python3.11/site-packages/django/db/utils.py",
> line 91, in __exit__
> raise dj_exc_value.with_traceback(traceback) from exc_value
> File "/opt/[redacted]/lib/python3.11/site-
> packages/django/db/backends/utils.py", line 103, in _execute
> return self.cursor.execute(sql)
> ^^^^^^^^^^^^^^^^^^^^^^^^
> django.db.utils.ProgrammingError: could not determine which collation to
> use for upper() function
> HINT: Use the COLLATE clause to set the collation explicitly.
> }}}
New description:
There is something up with how "_iexact" GeneratedField expressions are
turned into SQL for postgres 16.2. This does not occur with 16.1. This
leads to a "psycopg2.errors.IndeterminateCollation: could not determine
which collation to use for upper() function" error. This was caught in
our CI/CD env when I revved it to 16.2. Existing / old migrations failed.
This is a reduced examples from our code, with exception paths redacted
where necessary.
Model Definition:
{{{
class TestModel(models.Model):
test = models.CharField(max_length=254, blank=True)
test_gen =
models.GeneratedField(expression=models.Q(test__iexact='yes'),
output_field=models.BooleanField(), db_persist=True)
}}}
Migration content:
{{{
migrations.AddField(
model_name='testmodel',
name='test_gen',
field=models.GeneratedField(db_persist=True,
expression=models.Q(("test__iexact",'yes')),
output_field=models.BooleanField()),
)
}}}
Error:
{{{
Applying module.migration_name...Traceback (most recent call last):
File "/opt/venv/lib/python3.11/site-
packages/django/db/backends/utils.py", line 103, in _execute
return self.cursor.execute(sql)
^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.IndeterminateCollation: could not determine which
collation to use for upper() function
HINT: Use the COLLATE clause to set the collation explicitly.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/builds/[redacted]/manage.py", line 15, in <module>
execute_from_command_line(sys.argv)
File "/opt/[redacted]/lib/python3.11/site-
packages/django/core/management/__init__.py", line 442, in
execute_from_command_line
utility.execute()
File "/opt/[redacted]/lib/python3.11/site-
packages/django/core/management/__init__.py", line 436, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/opt/[redacted]/lib/python3.11/site-
packages/django/core/management/base.py", line 413, in run_from_argv
self.execute(*args, **cmd_options)
File "/opt/[redacted]/lib/python3.11/site-
packages/django/core/management/base.py", line 459, in execute
output = self.handle(*args, **options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/core/management/base.py", line 107, in wrapper
res = handle_func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/core/management/commands/migrate.py", line 356, in handle
post_migrate_state = executor.migrate(
^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/migrations/executor.py", line 135, in migrate
state = self._migrate_all_forwards(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/migrations/executor.py", line 167, in
_migrate_all_forwards
state = self.apply_migration(
^^^^^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/migrations/executor.py", line 252, in apply_migration
state = migration.apply(state, schema_editor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/migrations/migration.py", line 132, in apply
operation.database_forwards(
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/migrations/operations/fields.py", line 108, in
database_forwards
schema_editor.add_field(
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/backends/base/schema.py", line 750, in add_field
self.execute(sql, params)
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/backends/postgresql/schema.py", line 48, in execute
return super().execute(sql, None)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/backends/base/schema.py", line 201, in execute
cursor.execute(sql, params)
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/backends/utils.py", line 122, in execute
return super().execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/backends/utils.py", line 79, in execute
return self._execute_with_wrappers(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
return executor(sql, params, many, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/backends/utils.py", line 100, in _execute
with self.db.wrap_database_errors:
File "/opt/[redacted]/lib/python3.11/site-packages/django/db/utils.py",
line 91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/opt/[redacted]/lib/python3.11/site-
packages/django/db/backends/utils.py", line 103, in _execute
return self.cursor.execute(sql)
^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: could not determine which collation to
use for upper() function
HINT: Use the COLLATE clause to set the collation explicitly.
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/35194#comment:2>
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 on the web visit
https://groups.google.com/d/msgid/django-updates/0107018da688741a-933c9705-d2f8-4889-bdc0-7d24bac671bb-000000%40eu-central-1.amazonses.com.
