Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  needsinfo
 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 Sarah Boyce):

 * resolution:   => needsinfo
 * severity:  Release blocker => Normal
 * stage:  Accepted => Unreviewed
 * status:  new => closed

Comment:

 It is currently not clear whether the bug is on Postgres side or not.
 Until this is clear, we can keep the ticket in the "needsinfo" state as it
 best represents it's current status.
 Reopening the ticket did bring it to the attention of others and has
 helped us look into this a little more, but even in a closed state, the
 ticket is still "visible"/searchable.
 We can reopen once we better understand the root of the problem and it's
 clear that Django needs to implement changes.
-- 
Ticket URL: 
Django 
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/0107018f3d69e8d2-44c16cd0-6419-42f2-9350-d5887004-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Release blocker  |   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 Sarah Boyce):

 * has_patch:  1 => 0

-- 
Ticket URL: 
Django 
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/0107018ef15a56c7-829c7396-60a5-4806-bee2-010f6cf2e375-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Natalia Bidart):

 * cc: David Sanders (added)

Comment:

 David, I think you have successfully engaged with the PostgreSQL team/devs
 in the past, leading to productive conversations. Would you have some
 availability to reach out to them again to seek their assistance in
 debugging this specific issue we're encountering with PostgreSQL >= 12.18,
 13.14, 14.11, 15.6, 16.2?
-- 
Ticket URL: 
Django 
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/0107018ef15255de-c87fa60f-dfca-41b3-b386-9adfac692230-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Comment (by Simon Charette):

 It is effectively a solution but I'm not convinced this will do more good
 than harm.

 #3575 has been merged 16 years ago this means in between now and then
 thousands of projects were created and added a functional index on
 `UPPER("col")` to make `i(exact|contains|startwith)` use an index and the
 moment they upgrade to a minor version of 5.0 their database will start
 running slow queries as their indices will be unsuables.

 On the other hand we have a bug in a newly introduced feature for a very
 particular use case that might be affecting only a few users (must use
 generated field, must be on a latest version of Postgres, must use
 `i(exact|contains|startwith)`.

 I appreciate the intent to solve this issue but I think we need to dig
 deeper to truly understand ''why'' this is happening before jumping to
 conclusions here as there are no true urgency to get things right here;
 the ''release blocker'' assignment is self-imposed and nothing prevents us
 from deferring a solution to this problem to a future 5.0 release if we
 can't understand why this is happening before the May release as for all
 we know if might be a bug in Postgres itself.

 I tried reaching out on libera.chat#postgres IRC to get an answer but no
 one could answer me their (first time this happens) so I was planing to
 reach out to their mailing list this week but I might run out of time so
 if someone feels comfortable doing so please do.

 To summarize I think we should understand why this is happening before
 taking any potential harmful action here. For all we know many other
 functions and lookups could be affected and this is just the tip of the
 iceberg.
-- 
Ticket URL: 
Django 
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/0107018ef121a8ca-7c7d6268-be24-4287-8d76-7eb808070607-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Sarah Boyce):

 * has_patch:  0 => 1

Comment:

 It's an idea, we might get a better idea 
-- 
Ticket URL: 
Django 
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/0107018ef040fa06-451b7b09-2b5f-4571-a98d-6a699bc5af07-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Release blocker  |   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 Sarah Boyce):

 One option I have found (could be a bad idea) is to revert some of #3575.
 This was an optimisation where `ILIKE` was removed in preference of using
 `UPPER(field) LIKE UPPER('blah')`.
 If we use `ILIKE` I no longer get an error here. I guess the question is,
 the change of #3575 was implemented many years ago and the performance of
 Postgres in this case may have moved on.

 Looks a bit like:

 {{{
 diff --git a/django/db/backends/postgresql/base.py
 b/django/db/backends/postgresql/base.py
 index e97ab6aa89..4e3f7b3658 100644
 --- a/django/db/backends/postgresql/base.py
 +++ b/django/db/backends/postgresql/base.py
 @@ -154,7 +154,7 @@ class DatabaseWrapper(BaseDatabaseWrapper):
  "exact": "= %s",
  "iexact": "= UPPER(%s)",
  "contains": "LIKE %s",
 -"icontains": "LIKE UPPER(%s)",
 +"icontains": "ILIKE %s",
  "regex": "~ %s",
  "iregex": "~* %s",
  "gt": "> %s",
 @@ -163,8 +163,8 @@ class DatabaseWrapper(BaseDatabaseWrapper):
  "lte": "<= %s",
  "startswith": "LIKE %s",
  "endswith": "LIKE %s",
 -"istartswith": "LIKE UPPER(%s)",
 -"iendswith": "LIKE UPPER(%s)",
 +"istartswith": "ILIKE %s",
 +"iendswith": "ILIKE %s",
  }

  # The patterns below are used to generate SQL pattern lookup clauses
 when
 diff --git a/django/db/backends/postgresql/operations.py
 b/django/db/backends/postgresql/operations.py
 index 4b179ca83f..af2463b1d6 100644
 --- a/django/db/backends/postgresql/operations.py
 +++ b/django/db/backends/postgresql/operations.py
 @@ -172,10 +172,6 @@ class DatabaseOperations(BaseDatabaseOperations):
  else:
  lookup = "%s::text"

 -# Use UPPER(x) for case-insensitive lookups; it's faster.
 -if lookup_type in ("iexact", "icontains", "istartswith",
 "iendswith"):
 -lookup = "UPPER(%s)" % lookup
 -
  return lookup

  def no_limit_value(self):
 diff --git a/tests/schema/tests.py b/tests/schema/tests.py
 index 3a2947cf43..182e3486e0 100644
 --- a/tests/schema/tests.py
 +++ b/tests/schema/tests.py
 @@ -913,7 +913,7 @@ class SchemaTests(TransactionTestCase):
  editor.create_model(GeneratedFieldContainsModel)

  field = GeneratedField(
 -expression=Q(text__contains="foo"),
 +expression=Q(text__icontains="FOO"),
  db_persist=True,
  output_field=BooleanField(),
  )
 }}}
-- 
Ticket URL: 
Django 
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/0107018eec104e9c-59ccd268-55b4-447b-a6e1-cfd7120a561a-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Release blocker  |   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 Sarah Boyce <42296566+sarahboyce@…>):

 In [changeset:"5d95a1c35ef1375a7badcb217c36c5974d1e57ee" 5d95a1c]:
 {{{#!CommitTicketReference repository=""
 revision="5d95a1c35ef1375a7badcb217c36c5974d1e57ee"
 [5.0.x] Refs #35194 -- Adjusted a generated field test to work on Postgres
 15.6+.

 Postgres >= 12.18, 13.14, 14.11, 15.6, 16.2 changed the way the
 immutability
 of generated and default expressions is detected in
 postgres/postgres@743ddaf.

 The adjusted test semantic is presereved by switching from __icontains to
 __contains as both make use of a `%` literal which requires proper
 escaping.

 Refs #35336.

 Thanks bcail for the report.

 Backport of 73b62a21265c4a417004d64d13a896469e2558f3 from main.
 }}}
-- 
Ticket URL: 
Django 
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/0107018ed265d721-801aa4eb-94e2-4bb8-bc99-f12c9d027eaf-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Release blocker  |   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 Sarah Boyce <42296566+sarahboyce@…>):

 In [changeset:"73b62a21265c4a417004d64d13a896469e2558f3" 73b62a21]:
 {{{#!CommitTicketReference repository=""
 revision="73b62a21265c4a417004d64d13a896469e2558f3"
 Refs #35194 -- Adjusted a generated field test to work on Postgres 15.6+.

 Postgres >= 12.18, 13.14, 14.11, 15.6, 16.2 changed the way the
 immutability
 of generated and default expressions is detected in
 postgres/postgres@743ddaf.

 The adjusted test semantic is presereved by switching from __icontains to
 __contains as both make use of a `%` literal which requires proper
 escaping.

 Refs #35336.

 Thanks bcail for the report.
 }}}
-- 
Ticket URL: 
Django 
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/0107018ed1674368-b343eedb-9bca-460e-9cb4-a8de085638aa-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Release blocker  |   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 Sarah Boyce):

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

-- 
Ticket URL: 
Django 
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/0107018ed164bba0-6cde75e3-abfe-4116-9cb8-16a581d94993-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:  needsinfo
 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):

 * cc: Simon Charette (added)
 * severity:  Normal => Release blocker
 * stage:  Unreviewed => Accepted

Comment:

 Re-opening as a release blocker for 5.0 at it's a bug in a new feature
 (`GeneratedField`) after some sleuthing on #35368.

 The culprit is [https://www.postgresql.org/docs/release/15.6/ this change
 Postgres >= 12.18, 13.14, 14.11, 15.6, 16.2 released on 2024-02-08].

 > 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**.

 In essence the problem seems similar to #34955 as `UPPER` can return
 different value depending on the collation and thus is not immutable per-
 se?

 I've tried to come up with a workaround but I'm not sure what should be
 done. The following doesn't work either

 `UPPER("text" COLLATE "C") COLLATE "C" = UPPER('test' COLLATE "C") COLLATE
 "C"`

 so it's possible there might be a bug on the Postgres side as well? In all
 cases keeping this ticket open should bring visibility to the issue.

 A work around in the mean time is likely to use explicit collations
-- 
Ticket URL: 
Django 
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/0107018ecf63d0bc-1a66d58c-77a4-4eea-92ba-b6bbfe097a1e-00%40eu-central-1.amazonses.com.

Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation (was: Postgres 16.2 with _iexact leads to psycopg2.errors.IndeterminateCollation)

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-+-
 Reporter:  Aldalen  |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  5.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  needsinfo
 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 Mariusz Felisiak):

 * owner:  (none) => nobody
 * resolution:   => needsinfo
 * status:  new => closed
 * component:  contrib.postgres => Database layer (models, ORM)
 * summary:
 Postgres 16.2 with _iexact leads to
 psycopg2.errors.IndeterminateCollation
 => Postgres 16.2 with _iexact leads to IndeterminateCollation

Comment:

 Thanks for the report, this looks like a bug in PostgreSQL. I couldn't
 find anything related with this change in PostgreSQL 16.2 release notes.
 I've checked a few ways to define that kind of expression in the
 `GeneratedField` and it seems to be related to calling `UPPER()` on
 strings:
 - `models.GeneratedField(expression=models.Q(test__iexact='yes'),
 output_field=models.BooleanField(), db_persist=True)` crashes 
   - SQL:
 {{{#!sql
 "test_gen" boolean GENERATED ALWAYS AS (UPPER("test"::text) =
 UPPER('yes')) STORED
 }}}
 - `models.GeneratedField(expression=lookups.Exact(Upper("test"), "YES"),
 output_field=models.BooleanField(), db_persist=True)` works ✅
   - SQL:
 {{{#!sql
 "test_gen" boolean GENERATED ALWAYS AS (UPPER("test") = ('YES')) STORED
 }}}
 Removing `::text` doesn't change anything, it also crashes :
 {{{#!sql
 "test_gen" boolean GENERATED ALWAYS AS (UPPER("test") = UPPER('yes'))
 STORED
 }}}

 Please reopen the ticket if you provide details that this is an
 intentional behavior change in PostgreSQL.
-- 
Ticket URL: 
Django 
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/0107018da74eb99f-bc660763-acc3-4b5e-8a9b-cffd15e8a9bc-00%40eu-central-1.amazonses.com.