Message-ID: <f83f089ee1e9acd5dbbbf3353294d24e1f196e95.camel@j-davis.com>
Subject: Re: Order changes in PG16 since ICU introduction
From: Jeff Davis <pgsql@j-davis.com>
To: Tom Lane <tgl@sss.pgh.pa.us>, Regina Obe <lr@pcorp.us>
Cc: 'Peter Eisentraut' <peter.eisentraut@enterprisedb.com>, 'Sandro
 Santilli' <strk@kbt.io>, pgsql-hackers@lists.postgresql.org
Date: Fri, 21 Apr 2023 12:25:00 -0700
In-Reply-To: <3372735.1682101425@sss.pgh.pa.us>
References: <000b01d97465$c34bbd60$49e33820$@pcorp.us>
	 <3353947.1682092131@sss.pgh.pa.us> <ZELDOktA75yYG4Z2@hst.kbt.io>
	 <533d93c5-f604-8e5f-4a48-975c08c53d59@enterprisedb.com>
	 <3365333.1682098091@sss.pgh.pa.us>
	 <002d01d97477$f0395640$d0ac02c0$@pcorp.us>
	 <3367226.1682099185@sss.pgh.pa.us>
	 <003b01d9747a$910471c0$b30d5540$@pcorp.us>
	 <3368986.1682099982@sss.pgh.pa.us>
	 <000001d9747d$066b7560$13426020$@pcorp.us>
	 <3372735.1682101425@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu1 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/f83f089ee1e9acd5dbbbf3353294d24e1f196e95.camel%40j-davis.com>
Precedence: bulk

On Fri, 2023-04-21 at 14:23 -0400, Tom Lane wrote:
> postgres=3D# CREATE DATABASE test1 TEMPLATE=3Dtemplate0 ENCODING =3D 'UTF=
8'
> LOCALE =3D 'C';

...

> =C2=A0test1=C2=A0=C2=A0=C2=A0=C2=A0 | postgres | UTF8=C2=A0=C2=A0=C2=A0=
=C2=A0 | icu=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0 | C=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |
> C=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | en-US=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0 |=20
> (4 rows)
>=20
> Looks like the "pick en-US even when told not to" problem exists here
> too.

Both provider (ICU) and the icu locale (en-US) are inherited from
template0. The LOCALE parameter to CREATE DATABASE doesn't affect
either of those things, because there's a separate parameter
ICU_LOCALE.

This happens the same way in v15, and although it matches the
documentation technically, it is not a great user experience.

I have a couple ideas:

1. Introduce a "none" provider to separate the concept of C/POSIX
locales from the libc provider. It's not really using a provider
anyway, it's just using memcmp(), and I think it causes confusion to
combine them. Saying "LOCALE_PROVIDER=3Dnone" is less error-prone than
"LOCALE_PROVIDER=3Dlibc LOCALE=3D'C'".

2. Change the CREATE DATABASE syntax to catch these errors better at
the possible expense of backwards compatibility.

I am also having second thoughts about accepting "C" or "POSIX" as an
ICU locale and transforming it to "en-US-u-va-posix" in v16. It's not
terribly useful (why not just use memcmp()?), it's not fast in my
measurements (en-US is faster), so maybe it's better to just throw an
error and tell the user to use C (or provider=3Dnone as I suggest
above)?=C2=A0

Obviously the user could manually type "en-US-u-va-posix" if that's the
locale they want. Throwing an error would be a backwards-compatibility
issue, but in v15 an ICU locale of "C" just gives the root locale
anyway, which is probably not what they want.

Regards,
	Jeff Davis