Postgres schema test failure locally

[bcail]

The error is below. I bisected to commit 888b9042b3598bab6557c62de82505eec9ea62ed where the test started failing for me. I'm on Linux, using Postgres 15.

ERROR: test_add_generated_field (schema.tests.SchemaTests.test_add_generated_field)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/opt/local/src/django/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 "/opt/python-3.12/lib/python3.12/unittest/case.py", line 58, in testPartExecutor
    yield
  File "/opt/python-3.12/lib/python3.12/unittest/case.py", line 636, in run
    self._callTestMethod(testMethod)
    ^^^^^^^^^^^^^^^^^
  File "/opt/python-3.12/lib/python3.12/unittest/case.py", line 589, in _callTestMethod
    if method() is not None:
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/django/test/utils.py", line 446, in inner
    return func(*args, **kwargs)
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/django/test/testcases.py", line 1548, in skip_wrapper
    return test_func(*args, **kwargs)
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/tests/schema/tests.py", line 923, in test_add_generated_field_contains
    editor.add_field(GeneratedFieldContainsModel, field)
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/django/db/backends/base/schema.py", line 754, in add_field
    self.execute(sql, params or None)
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
    ^^^^^^^^^^^^^^^^^
  File "/opt/local/src/django/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.

[Natalia Bidart]

Hello, thank you for your report!

Could you please provide more details and instructions on how to reproduce? I tested locally with PG 16.1 and also the Jenkins CI is running PG 13 and in both setups the mentioned test works OK. Could this be something specific from your environment or your models?

Closing as worksforme for now but do let us know if you can provide concrete steps to reproduce this.

[bcail]

I don't know the details of what's going wrong - I've been able to run django's test suite fine, on sqlite & postgres, and then it started failing, and I found that it was that commit where the problem started. I was using psycopg2, and I just tried psycopg3, and it still failed.

Here is another detail, though: the test name that django gives as failing is "test_add_generated_field" - but in the stacktrace, it points to the test added in that commit ("test_add_generated_field_contains") - it seems that django's printing the wrong test name in the error summary. If I set parallel=1 for the tests, then the correct test name gets shown in the summary.

[Simon Charette]

Hello there!

Could you provide the exact version of psycopg2 and psycopg3 you are using to reproduce. I'm also struggling to reproduce on my side.

There might some ​Postgres collation shenanigans involving the distro you are using as well. Can you also reproduce with Docker?

See #35194 which seems related.

[bcail]

psycopg => 3.1.18, psycopg2 => 2.9.9

I'm on Debian 12 Bookworm - maybe there's an issue related to Debian's settings?

[Simon Charette]

I'm able to reproduce against the Postgres 16.2 Docker image just like Mariuzs did a few weeks ago.

Not sure what the proper solution is though the failure modes seems a bit arbitrary

1. UPPER("test"::text) = UPPER('yes') 💥
2. UPPER("test") = ('YES') ✅
3. UPPER("test") = UPPER('yes') 💥

I guess we could adapt the test to avoid using UPPER to address this immediate issue as it's not relevant to the test (it was interested in covering % usage)

tests/schema/tests.py

@@ -913 +913 @@
             editor.create_model(GeneratedFieldContainsModel)
 
         field = GeneratedField(
-            expression=Q(text__icontains="FOO"),
+            expression=Q(text__contains="foo"),
             db_persist=True,
             output_field=BooleanField(),
         )

And then re-open #35194 for further investigation into which COLLATE incantations must be used for generated expressions. In a sense the problem is very similar to the CONCAT vs || problem we ran into for GeneratedField where CONCAT is not immutable (#34955). Just like CONCAT the UPPER functions defaults to runtime information that must be explicitly declared when creating generate expressions.

[Natalia Bidart]

Thank you Simon for that ticket pointer, your plan makes sense (reopening #35194 and marking this as a dupe).

bcail, could you please double check that you are getting the error in PG 15? or is it 16.2 like the other ticket and what Simon was able to reproduce?

[Simon Charette]

Nailed it down to ​a change in Postgres 15.6 released on 2024-02-08 so it makes sense that bcail ran on that only recently against PG 15.

Fix function volatility checking for GENERATED and DEFAULT expressions (Tom Lane)

These places could fail to detect insertion of a volatile function default-argument expression, or decide that a polymorphic function is volatile although it is actually immutable on the datatype of interest. This could lead to improperly rejecting or accepting a GENERATED clause, or to mistakenly applying the constant-default-value optimization in ALTER TABLE ADD COLUMN.

[Natalia Bidart]

Simon, is my assumption correct that a PR is attached to this to separate the fixing of the test introduced in 888b9042b3598bab6557c62de82505eec9ea62ed vs the fix itself for the non-determinism of UPPER?
If yes, I would suggest that, instead, the PR Refs #35194 and we close this as dupe because as I understand, the test failure is just a symptom of the issue reported there. Thanks!

[Simon Charette]

Works for me. I'll reword the commit message so you can close this one as a dupe.

[Natalia Bidart]

Thank you!
Closing as duplicate of #35194