Re: fixing tsearch locale support

On 18/08/2025 18:56, Daniel Verite wrote:
>> There is a PG18 open item to document this possible upgrade incompatibility.
>>
>> I think the following text could be added to the release notes:
>>
>> """
>> The locale implementation underlying full-text search was improved. It
>> now observes the locale provider configured for the database. It was
>> previously hardcoded to use the configured libc LC_CTYPE setting
>> [...]
>
> That sounds misleading because LC_CTYPE is still used in 18.
>
> To illustrate in an ICU database, the parser will classify "Em Dash"
> as a separator or not depending on LC_CTYPE.
>
> with LC_CTYPE=C
>
> => select alias, token,lexemes from ts_debug('simple', U&'ABCD\2014EFGH');
> alias | token | lexemes
> -------+-----------+-------------
> word | ABCD—EFGH | {abcd—efgh}
>
>
> with LC_CTYPE=en_US.utf8 (glibc 2.35):
>
> => select alias, token,lexemes from ts_debug('simple', U&'ABCD\2014EFGH');
> alias | token | lexemes
> -----------+-------+---------
> asciiword | ABCD | {abcd}
> blank | — |
> asciiword | EFGH | {efgh}
>
>
> OTOH lower casing uses LC_CTYPE in 17, but not in 18, leading
> to better lexemes.
>
> pg17, ICU locale, LC_TYPE=C
>
> => select alias, token,lexemes from ts_debug('simple', 'ÉTÉ');
> alias | token | lexemes
> -------+-------+---------
> word | ÉTÉ | {ÉtÉ}
>
> pg18, ICU locale, LC_TYPE=C
>
> select alias, token,lexemes from ts_debug('simple', 'ÉTÉ');
> alias | token | lexemes
> -------+-------+---------
> word | ÉTÉ | {été}
>
> So maybe the release notes should say
> "now observes the locale provider configured for the database to
> convert strings to lower case".

Is it only used for converting to lower case, or is there any other
operations that need to be mentioned? Converting to upper case too I
presume. (I haven't been following this thread)

We only support two collation providers, libc and ICU right? That makes
Peter's phrasing "In database clusters that use a locale provider other
than libc ..." an unnecessarily complicated way of saying ICU.

Putting those two changes together:

"""
The locale implementation underlying full-text search was improved. It
now observes the collation provider configured for the database for
converting strings to upper and lower case. It was previously hardcoded
to use libc. In databases that use the ICU collation provider and where
the configured ICU locale behaves differently from the LC_CTYPE setting
configured for the database, this could cause changes in behavior of
some functions related to full-text search as well as the pg_trgm
extension. When upgrading such database clusters using pg_upgrade, it
is recommended to reindex all indexes related to full-text search and
pg_trgm after the upgrade.
"""

I wonder if it's clear enough that this applies to full-text search, not
upper/lower case conversions in general. (Is that true?)

It's pretty urgent to get the release notes in shape, people are testing
upgrades with the betas already...

- Heikki