Message-ID: <d3b8b238e99f672e2c170eb7ab14ee61fe7fa3a3.camel@j-davis.com>
Subject: Re: Built-in CTYPE provider
From: Jeff Davis <pgsql@j-davis.com>
To: Peter Eisentraut <peter@eisentraut.org>, Daniel Verite
	 <daniel@manitou-mail.org>
Cc: Robert Haas <robertmhaas@gmail.com>, Jeremy Schneider
	 <schneider@ardentperf.com>, pgsql-hackers@postgresql.org
Date: Wed, 03 Apr 2024 16:19:02 -0700
In-Reply-To: <f7a98d94-1708-4d51-b7e9-0d07f7a1e46b@eisentraut.org>
References: <6f3e94c0-f174-4380-9b69-072f8a838881@manitou-mail.org>
	 <4a69d067374d2f6bfb66f5bfb2ab9a020493d49f.camel@j-davis.com>
	 <3bc653b5d562ae9e2838b11cb696816c328a489a.camel@j-davis.com>
	 <163f4e2190cdf67f67016044e503c5004547e5a9.camel@j-davis.com>
	 <6bdb98e68b2b05aa71f7f934e227738eac84ecee.camel@j-davis.com>
	 <19b34a70-f5cf-4faf-88dc-917db44ed48d@eisentraut.org>
	 <846a7e1fa2024918b58ff7583523a2f3de9a11b4.camel@j-davis.com>
	 <3117d30aa911b408b90420f4f280fa0c3b5851be.camel@j-davis.com>
	 <4135cf11-206d-40ed-96c0-9363c1232379@eisentraut.org>
	 <7451f81ba0cb512222ab759de8ca1cffe44e9acb.camel@j-davis.com>
	 <1f309153-8198-4efa-86dd-8c304ec0040c@eisentraut.org>
	 <a21b515000f121244db2a5570d4292d19df23379.camel@j-davis.com>
	 <49f18979-bef5-4cf0-bf3d-8a5ed323f470@eisentraut.org>
	 <69f832595838e40c1b071a5e750665190ba301bf.camel@j-davis.com>
	 <4c9eea58-08a1-4629-a004-439e2cf12de8@eisentraut.org>
	 <1466f20c38f4433c98a950acdaf2c96daf7ce442.camel@j-davis.com>
	 <f7a98d94-1708-4d51-b7e9-0d07f7a1e46b@eisentraut.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/d3b8b238e99f672e2c170eb7ab14ee61fe7fa3a3.camel%40j-davis.com>
Precedence: bulk

On Tue, 2024-03-26 at 08:14 +0100, Peter Eisentraut wrote:
>=20
> Full vs. simple case mapping is more of a legacy compatibility
> question,=20
> in my mind.=C2=A0 There is some expectation/precedent that C.UTF-8 uses=
=20
> simple case mapping, but beyond that, I don't see a reason why
> someone=20
> would want to explicitly opt for simple case mapping, other than if
> they=20
> need length preservation or something, but if they need that, then
> they=20
> are going to be in a world of pain in Unicode anyway.

I mostly agree, though there are some other purposes for the simple
mapping:

* a substitute for case folding: lower() with simple case mapping will
work better for that purpose than lower() with full case mapping (after
we have casefold(), this won't be a problem)

* simple case mapping is conceptually simpler, and that's a benefit by
itself in some situations -- maybe the 1:1 assumption exists other
places in their application

> > There's also another reason to consider it an argument rather than
> > a
> > collation property, which is that it might be dependent on some
> > other
> > field in a row. I could imagine someone wanting to do:
> >=20
> > =C2=A0=C2=A0=C2=A0 SELECT
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 UPPER(some_field,
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 full=
 =3D> true,
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 dotl=
ess_i =3D> CASE other_field WHEN ...)
> > =C2=A0=C2=A0=C2=A0 FROM ...
>=20
> Can you index this usefully?=C2=A0 It would only work if the user query=
=20
> matches exactly this pattern?

In that example, UPPER is used in the target list -- the WHERE clause
might be indexable. The UPPER is just used for display purposes, and
may depend on some locale settings stored in another table associated
with a particular user.

Regards,
	Jeff Davis