#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 django-updates+unsubscr...@googlegroups.com.
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.
