Re: ICU for global collation

Hello!

I discovered an interesting behaviour during installcheck runs when the
cluster was initialized with ICU locale provider:

$ initdb --locale-provider icu --icu-locale en-US -D data &&
pg_ctl -D data -l logfile start

1) The ECPG tests fail because they use the SQL_ASCII encoding [1], the
database template0 uses the ICU locale provider and SQL_ASCII is not
supported by ICU:

$ make -C src/interfaces/ecpg/ installcheck
...
============== creating database "ecpg1_regression" ==============
ERROR: encoding "SQL_ASCII" is not supported with ICU provider
ERROR: database "ecpg1_regression" does not exist
command failed: "/home/marina/postgresql/master/my/inst/bin/psql" -X -c
"CREATE DATABASE \"ecpg1_regression\" TEMPLATE=template0
ENCODING='SQL_ASCII'" -c "ALTER DATABASE \"ecpg1_regression\" SET
lc_messages TO 'C';ALTER DATABASE \"ecpg1_regression\" SET lc_monetary
TO 'C';ALTER DATABASE \"ecpg1_regression\" SET lc_numeric TO 'C';ALTER
DATABASE \"ecpg1_regression\" SET lc_time TO 'C';ALTER DATABASE
\"ecpg1_regression\" SET bytea_output TO 'hex';ALTER DATABASE
\"ecpg1_regression\" SET timezone_abbreviations TO 'Default';"
"postgres"

2) The option --no-locale in pg_regress is described as "use C locale"
[2]. But in this case the created databases actually use the ICU locale
provider with the ICU cluster locale from template0 (see
diff_check_backend_used_provider.patch):

$ make NO_LOCALE=1 installcheck

In regression.diffs:

diff -U3
/home/marina/postgresql/master/src/test/regress/expected/test_setup.out
/home/marina/postgresql/master/src/test/regress/results/test_setup.out
---
/home/marina/postgresql/master/src/test/regress/expected/test_setup.out 2022-09-27
05:31:27.674628815 +0300
+++
/home/marina/postgresql/master/src/test/regress/results/test_setup.out 2022-10-21
15:09:31.232992885 +0300
@@ -143,6 +143,798 @@
\set filename :abs_srcdir '/data/person.data'
COPY person FROM :'filename';
VACUUM ANALYZE person;
+NOTICE: varstrfastcmp_locale sss->collate_c 0 sss->locale 0xefacd0
+NOTICE: varstrfastcmp_locale sss->locale->provider i
+NOTICE: varstrfastcmp_locale sss->locale->info.icu.locale en-US
...

The patch diff_fix_pg_regress_create_database.patch fixes both issues
for me.

[1]
https://github.com/postgres/postgres/blob/ce20f8b9f4354b46b40fd6ebf7ce5c37d08747e0/src/interfaces/ecpg/test/Makefile#L18
[2]
https://github.com/postgres/postgres/blob/ce20f8b9f4354b46b40fd6ebf7ce5c37d08747e0/src/test/regress/pg_regress.c#L1992

--
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company