Content-Type: text/plain; charset="iso-8859-15"
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Subject: Re: ICU for global collation
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Peter Eisentraut" <peter.eisentraut@2ndquadrant.com>
Cc: "pgsql-hackers" <pgsql-hackers@postgresql.org>
In-Reply-To: <5e756dd6-0e91-d778-96fd-b1bcb06c161a@2ndquadrant.com>
Date: Tue, 17 Sep 2019 15:08:36 +0200
Message-Id: <5d807706-60a2-4e56-bc59-eef9e7deb138@manitou-mail.org>
Precedence: bulk

 Hi,

When trying databases defined with ICU locales, I see that backends
that serve such databases seem to have their LC_CTYPE inherited from
the environment (as opposed to a per-database fixed value).

That's a problem for the backend code that depends on libc functions
that themselves depend on LC_CTYPE, such as the full text search parser
and dictionaries.

For instance, if you start the instance with a C locale
(LC_ALL=3DC pg_ctl...) , and tries to use FTS in an ICU UTF-8 database,
it doesn't work:

template1=3D# create database "fr-utf8"=20
  template 'template0' encoding UTF8
  locale 'fr'
  collation_provider 'icu';

template1=3D# \c fr-utf8
You are now connected to database "fr-utf8" as user "daniel".

fr-utf8=3D# show lc_ctype;
 lc_ctype=20
----------
 fr
(1 row)

fr-utf8=3D# select to_tsvector('=E9t=E9');
ERROR:	invalid multibyte character for locale
HINT:  The server's LC_CTYPE locale is probably incompatible with the
database encoding.

If I peek into the "real" LC_CTYPE when connected to this database,
I can see it's "C":

fr-utf8=3D# create extension plperl;
CREATE EXTENSION

fr-utf8=3D# create function lc_ctype() returns text as '$ENV{LC_CTYPE};'
  language plperl;
CREATE FUNCTION

fr-utf8=3D# select lc_ctype();
 lc_ctype=20
----------
 C


Best regards,
--=20
Daniel V=E9rit=E9
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite