Content-Type: text/plain; charset="iso-8859-15"
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Subject: Re: insensitive collations
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Andreas Karlsson" <andreas@proxel.se>
Cc: "Peter Eisentraut" <peter.eisentraut@2ndquadrant.com>,"pgsql-hackers"
 <pgsql-hackers@postgresql.org>
In-Reply-To: <da340c4b-24d0-8ff1-54d2-5b023eb81e3c@proxel.se>
Date: Mon, 14 Jan 2019 17:21:34 +0100
Message-Id: <ef84c67b-cfa9-4a3f-b0ae-e9ff81e9d948@manitou-mail.org>
Precedence: bulk

	Andreas Karlsson wrote:

> > Nondeterministic collations do address this by allowing canonically
> > equivalent code point sequences to compare as equal.  You still need a
> > collation implementation that actually does compare them as equal; ICU
> > does this, glibc does not AFAICT.
>=20
> Ah, right! You could use -ks-identic[1] for this.

Strings that differ like that are considered equal even at this level:

postgres=3D# create collation identic  (locale=3D'und-u-ks-identic',
    provider=3D'icu', deterministic=3Dfalse);
CREATE COLLATION

postgres=3D# select '=E9' =3D E'e\u0301' collate "identic";
 ?column?=20
----------
 t
(1 row)


There's a separate setting "colNormalization", or "kk" in BCP 47

From=20
http://www.unicode.org/reports/tr35/tr35-collation.html#Normalization_Setti=
ng

  "The UCA always normalizes input strings into NFD form before the
  rest of the algorithm. However, this results in poor performance.
  With normalization=3Doff, strings that are in [FCD] and do not contain
  Tibetan precomposed vowels (U+0F73, U+0F75, U+0F81) should sort
  correctly. With normalization=3Don, an implementation that does not
  normalize to NFD must at least perform an incremental FCD check and
  normalize substrings as necessary"

But even setting this to false does not mean that NFD and NFC forms
of the same text compare as different:

postgres=3D# create collation identickk  (locale=3D'und-u-ks-identic-kk-fal=
se',
    provider=3D'icu', deterministic=3Dfalse);
CREATE COLLATION

postgres=3D# select '=E9' =3D E'e\u0301' collate "identickk";
 ?column?=20
----------
 t
(1 row)

AFAIU such strings may only compare as different when they're not
in FCD form (http://unicode.org/notes/tn5/#FCD)

There are also ICU-specific explanations about FCD here:
http://source.icu-project.org/repos/icu/icuhtml/trunk/design/collation/ICU_=
collation_design.htm#Normalization

It looks like setting colNormalization to false might provide a
performance benefit when you know your contents are in FCD
form, which is mostly the case according to ICU:

  "Note that all NFD strings are in FCD, and in practice most NFC
  strings will also be in FCD; for that matter most strings (of whatever
  ilk) will be in FCD.
  We guarantee that if any input strings are in FCD, that we will get
  the right results in collation without having to normalize".


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