Thread: Fix database creation during installchecks for ICU cluster
Hello! This is a copy of [1] moved to a separated thread for Commitfest.. I discovered an interesting behaviour during installcheck runs on PG 15+ 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 [2], 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" [3]. 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.txt): $ 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 v1-0001-Fix-database-creation-during-installchecks-for-IC.patch fixes both issues for me. [1] https://www.postgresql.org/message-id/727b5d5160f845dcf5e0818e625a6e56%40postgrespro.ru [2] https://github.com/postgres/postgres/blob/ce20f8b9f4354b46b40fd6ebf7ce5c37d08747e0/src/interfaces/ecpg/test/Makefile#L18 [3] 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
Attachment
Hi, Thanks for the patch! On 10/29/22 12:54, Marina Polyakova wrote: > > 1) The ECPG tests fail because they use the SQL_ASCII encoding [2], > 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" > I can confirm that same error happens on my end and your patch fixes the issue. But, do ECPG tests really require SQL_ASCII encoding? I removed ECPG tests' encoding line [1], rebuilt it and 'make -C src/interfaces/ecpg/ installcheck' passed without applying your patch. > > 2) The option --no-locale in pg_regress is described as "use C locale" > [3]. 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.txt): > > $ make NO_LOCALE=1 installcheck This works on my end without applying your patch. Commands I used are: $ ./configure --with-icu --prefix=$PWD/build_dir $ make && make install && export PATH=$PWD/build_dir/bin:$PATH $ initdb --locale-provider icu --icu-locale en-US -D data && pg_ctl -D data -l logfile start $ make NO_LOCALE=1 installcheck [1] https://github.com/postgres/postgres/blob/ce20f8b9f4354b46b40fd6ebf7ce5c37d08747e0/src/interfaces/ecpg/test/Makefile#L18 Regards, Nazir Bilal Yavuz
On Tue, 29 Nov 2022 at 20:24, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote: > > Hi, > > Thanks for the patch! > > > On 10/29/22 12:54, Marina Polyakova wrote: > > > > 1) The ECPG tests fail because they use the SQL_ASCII encoding [2], > > 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" > > > > I can confirm that same error happens on my end and your patch fixes the > issue. But, do ECPG tests really require SQL_ASCII encoding? I removed > ECPG tests' encoding line [1], rebuilt it and 'make -C > src/interfaces/ecpg/ installcheck' passed without applying your patch. > > > > > > 2) The option --no-locale in pg_regress is described as "use C locale" > > [3]. 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.txt): > > > > $ make NO_LOCALE=1 installcheck > > > This works on my end without applying your patch. Commands I used are: > > $ ./configure --with-icu --prefix=$PWD/build_dir > $ make && make install && export PATH=$PWD/build_dir/bin:$PATH > $ initdb --locale-provider icu --icu-locale en-US -D data && pg_ctl -D > data -l logfile start > $ make NO_LOCALE=1 installcheck Hi Marina Polyakova, Since it is working without your patch, Is this patch required for any other scenarios? Regards, Vignesh
On Tue, 17 Jan 2023 at 17:17, vignesh C <vignesh21@gmail.com> wrote: > > On Tue, 29 Nov 2022 at 20:24, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote: > > > > Hi, > > > > Thanks for the patch! > > > > > > On 10/29/22 12:54, Marina Polyakova wrote: > > > > > > 1) The ECPG tests fail because they use the SQL_ASCII encoding [2], > > > 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" > > > > > > > I can confirm that same error happens on my end and your patch fixes the > > issue. But, do ECPG tests really require SQL_ASCII encoding? I removed > > ECPG tests' encoding line [1], rebuilt it and 'make -C > > src/interfaces/ecpg/ installcheck' passed without applying your patch. > > > > > > > > > > 2) The option --no-locale in pg_regress is described as "use C locale" > > > [3]. 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.txt): > > > > > > $ make NO_LOCALE=1 installcheck > > > > > > This works on my end without applying your patch. Commands I used are: > > > > $ ./configure --with-icu --prefix=$PWD/build_dir > > $ make && make install && export PATH=$PWD/build_dir/bin:$PATH > > $ initdb --locale-provider icu --icu-locale en-US -D data && pg_ctl -D > > data -l logfile start > > $ make NO_LOCALE=1 installcheck > > Hi Marina Polyakova, > > Since it is working without your patch, Is this patch required for any > other scenarios? There has been no updates on this thread for some time, so this has been switched as Returned with Feedback. Feel free to open it in the next commitfest if you plan to continue on this. Regards, Vignesh