Re: BUG #17571: PostgreSQL 15b2 ICU collation defined at db level sorts differently in collate clause vs implicit

On Thu, Aug 04, 2022 at 03:59:12PM +0000, Daniel Halsey wrote:
>
> This will be frustrating to work around, since it'll require injecting
> COLLATE sub-clauses for all order by clauses (or like clauses, if we
> re-define our columns to use a non-deterministic collation).
> Is support for non-deterministic collation at the db level on the roadmap?

No one is working on that at the moment as far as I know.

The problem is that we would need to support LIKE and regex for non
deterministic collation first, as those are use in system views.

> Is there a query to determine what the actual/effective collation settings
> are (per the underlying provider) for a given db (since
> pg_database.daticulocale x pg_catalog.pg_collation.collname doesn't
> necessarily return what's going to be used)?

I think there's no misunderstanding here, pg_database.daticulocale isn't
supposed to refer to a collation name in the database, it's the actual locale
string passed to ICU, ie. the same as pg_collation.colliculocale.

So you have to check what ICU will exactly be doing for a given locale string,
which is a bit troublesome as it tends to accept anything and fallback to its
"root" locale.

You can also use Daniel Vérité's excellent icu_ext extension at
https://github.com/dverite/icu_ext.  This provides a lot of useful function,
including icu_collation_attributes(), which for the "und-sorttest-x-icu" locale
string returns:

=# SELECT * FROM icu_collation_attributes('und-sorttest-x-icu');
  attribute  |                    value
-------------+----------------------------------------------
 displayname | Unknown language (SORTTEST, Private-Use=icu)
 kn          | false
 kb          | false
 kk          | false
 ka          | noignore
 ks          | level3
 kf          | false
 kc          | false
 kv          | punct
 version     | 153.112
(10 rows)