[Django] #35368: Postgres schema test failure locally

[Django]

#35368: Postgres schema test failure locally
-----------------------------------------+------------------------
Reporter: bcail | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: dev
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 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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35368>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35368: Postgres schema test failure locally

-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) | Resolution:
Severity: Normal | worksforme

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 Natalia Bidart):

* component: Uncategorized => Database layer (models, ORM)
* resolution: => worksforme
* status: new => closed
* type: Uncategorized => Bug

Comment:

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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:1>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) | Resolution:
Severity: Normal | worksforme
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 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:2>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) | Resolution:
Severity: Normal | worksforme
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):

* cc: Simon Charette (added)

Comment:

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 [https://www.postgresql.org/docs/current/collation.html
#COLLATION-MANAGING-PREDEFINED Postgres collation shenanigans involving
the distro you are using as well]. Can you also reproduce with Docker?

See #35194 which seems related.
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:3>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) | Resolution:
Severity: Normal | worksforme
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 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?
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:4>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) | Resolution:
Severity: Normal | worksforme

Keywords: | Triage Stage: Accepted

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

Changes (by Simon Charette):

* stage: Unreviewed => Accepted

Comment:

I'm able to reproduce against the Postgres 16.2 Docker image
[https://code.djangoproject.com/ticket/35194#comment:3 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)

{{{#!diff
diff --git a/tests/schema/tests.py b/tests/schema/tests.py
index 86ac7382cc..ff126d446a 100644
--- a/tests/schema/tests.py
+++ b/tests/schema/tests.py
@@ -913,7 +913,7 @@ class Meta:
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:5>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody

Type: Bug | Status: new

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution:

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

Changes (by bcail):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:6>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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?
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:7>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

Nailed it down to [https://www.postgresql.org/docs/release/15.6/ 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**.
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:8>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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!
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:9>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

Works for me. I'll reword the commit message so you can close this one as
a dupe.
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:10>

[Django]

#35368: Postgres schema test failure locally
-------------------------------------+-------------------------------------
Reporter: bcail | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: duplicate

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

Changes (by Natalia Bidart):

* resolution: => duplicate

* status: new => closed

Comment:

Thank you!
Closing as duplicate of #35194
--
Ticket URL: <https://code.djangoproject.com/ticket/35368#comment:11>