Thread: Issues with inconsistent COLLATION installation
We are having issues with some databases getting our locales generated. We are using Ubuntu 18.04 and postgresql 10.8.
Example:
from bash prompt:
$ locale -a | egrep fr
fr_BE
fr_BE@euro
fr_BE.iso88591
fr_BE.iso885915@euro
fr_BE.utf8
fr_CA
fr_CA.iso88591
fr_CA.utf8
fr_CH
fr_CH.iso88591
fr_CH.utf8
french
fr_FR
fr_FR@euro
fr_FR.iso88591
fr_FR.iso885915@euro
fr_FR.utf8
fr_LU
fr_LU@euro
fr_LU.iso88591
fr_LU.iso885915@euro
fr_LU.utf8
fr_BE
fr_BE@euro
fr_BE.iso88591
fr_BE.iso885915@euro
fr_BE.utf8
fr_CA
fr_CA.iso88591
fr_CA.utf8
fr_CH
fr_CH.iso88591
fr_CH.utf8
french
fr_FR
fr_FR@euro
fr_FR.iso88591
fr_FR.iso885915@euro
fr_FR.utf8
fr_LU
fr_LU@euro
fr_LU.iso88591
fr_LU.iso885915@euro
fr_LU.utf8
from psql prompt:
[local] cnemelka@postgres=# create collation french (provider=libc,locale='fr_FR.utf8');
ERROR: 22023: could not create locale "fr_FR.utf8": No such file or directory
DETAIL: The operating system could not find any locale data for the locale name "fr_FR.utf8".
LOCATION: report_newlocale_failure, pg_locale.c:1312
ERROR: 22023: could not create locale "fr_FR.utf8": No such file or directory
DETAIL: The operating system could not find any locale data for the locale name "fr_FR.utf8".
LOCATION: report_newlocale_failure, pg_locale.c:1312
Anyone having similar issues or know of the solution?
TIA,
--cnemelka
We have already run pg_import_system_collations('pg_catalog')
--cnemelka
On Mon, Sep 9, 2019 at 12:43 PM Cory Nemelka <cnemelka@gmail.com> wrote:
We are having issues with some databases getting our locales generated. We are using Ubuntu 18.04 and postgresql 10.8.Example:from bash prompt:$ locale -a | egrep fr
fr_BE
fr_BE@euro
fr_BE.iso88591
fr_BE.iso885915@euro
fr_BE.utf8
fr_CA
fr_CA.iso88591
fr_CA.utf8
fr_CH
fr_CH.iso88591
fr_CH.utf8
french
fr_FR
fr_FR@euro
fr_FR.iso88591
fr_FR.iso885915@euro
fr_FR.utf8
fr_LU
fr_LU@euro
fr_LU.iso88591
fr_LU.iso885915@euro
fr_LU.utf8from psql prompt:[local] cnemelka@postgres=# create collation french (provider=libc,locale='fr_FR.utf8');
ERROR: 22023: could not create locale "fr_FR.utf8": No such file or directory
DETAIL: The operating system could not find any locale data for the locale name "fr_FR.utf8".
LOCATION: report_newlocale_failure, pg_locale.c:1312Anyone having similar issues or know of the solution?TIA,--cnemelka
Here is encoding for existing database:
List of databases
┌───────────┬──────────┬──────────┬─────────────┬─────────────┬───────────────────────┐
│ Name │ Owner │ Encoding │ Collate │ Ctype │ Access privileges │
├───────────┼──────────┼──────────┼─────────────┼─────────────┼───────────────────────┤
│ thedatabase │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ postgres │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ template0 │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres ↵│
│ │ │ │ │ │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ postgres=CTc/postgres↵│
│ │ │ │ │ │ =c/postgres │
└───────────┴──────────┴──────────┴─────────────┴─────────────┴───────────────────────┘
┌───────────┬──────────┬──────────┬─────────────┬─────────────┬───────────────────────┐
│ Name │ Owner │ Encoding │ Collate │ Ctype │ Access privileges │
├───────────┼──────────┼──────────┼─────────────┼─────────────┼───────────────────────┤
│ thedatabase │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ postgres │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ template0 │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres ↵│
│ │ │ │ │ │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ postgres=CTc/postgres↵│
│ │ │ │ │ │ =c/postgres │
└───────────┴──────────┴──────────┴─────────────┴─────────────┴───────────────────────┘
--cnemelka
On Mon, Sep 9, 2019 at 12:45 PM Cory Nemelka <cnemelka@gmail.com> wrote:
We have already run pg_import_system_collations('pg_catalog')--cnemelkaOn Mon, Sep 9, 2019 at 12:43 PM Cory Nemelka <cnemelka@gmail.com> wrote:We are having issues with some databases getting our locales generated. We are using Ubuntu 18.04 and postgresql 10.8.Example:from bash prompt:$ locale -a | egrep fr
fr_BE
fr_BE@euro
fr_BE.iso88591
fr_BE.iso885915@euro
fr_BE.utf8
fr_CA
fr_CA.iso88591
fr_CA.utf8
fr_CH
fr_CH.iso88591
fr_CH.utf8
french
fr_FR
fr_FR@euro
fr_FR.iso88591
fr_FR.iso885915@euro
fr_FR.utf8
fr_LU
fr_LU@euro
fr_LU.iso88591
fr_LU.iso885915@euro
fr_LU.utf8from psql prompt:[local] cnemelka@postgres=# create collation french (provider=libc,locale='fr_FR.utf8');
ERROR: 22023: could not create locale "fr_FR.utf8": No such file or directory
DETAIL: The operating system could not find any locale data for the locale name "fr_FR.utf8".
LOCATION: report_newlocale_failure, pg_locale.c:1312Anyone having similar issues or know of the solution?TIA,--cnemelka
Cory Nemelka <cnemelka@gmail.com> writes: > Here is encoding for existing database: > List of databases > ┌───────────┬──────────┬──────────┬─────────────┬─────────────┬───────────────────────┐ > │ Name │ Owner │ Encoding │ Collate │ Ctype │ Access > privileges │ > ├───────────┼──────────┼──────────┼─────────────┼─────────────┼───────────────────────┤ > │ thedatabase │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ > │ Hmm. It's interesting that the collate/ctype locales say "UTF-8" where locale -a says "utf8". It's not apparent to me what relation that might have to your observed problem, but it suggests that this cluster doesn't exactly have a pristine history. Did you perhaps try to move the data files from one platform to another? regards, tom lane
Cory Nemelka wrote: > ERROR: 22023: could not create locale "fr_FR.utf8": No such file or > directory > DETAIL: The operating system could not find any locale data for the locale > name "fr_FR.utf8". > LOCATION: report_newlocale_failure, pg_locale.c:1312 I can reproduce this by creating a new locale *after* starting PostgreSQL and trying to use it before a restart. Example with Ubuntu 18.04 and Postgres 11.5: # locale-gen es_ES.utf8 Generating locales (this might take a while)... es_ES.UTF-8... done Generation complete. Immediately refering to the locale will fail: $ sudo -u postgres psql -d test -U postgres test=# create collation es (provider=libc, locale='es_ES.utf8'); ERROR: could not create locale "es_ES.utf8": No such file or directory DÉTAIL : The operating system could not find any locale data for the locale name "es_ES.utf8". Now restart postgresql $ sudo /etc/init.d/postgresql restart [ ok ] Restarting postgresql (via systemctl): postgresql.service. And now it works: $ sudo -u postgres psql -d test -U postgres test=# create collation es (provider=libc, locale='es_ES.utf8'); CREATE COLLATION test=# select * from pg_collation where collname='es' \gx -[ RECORD 1 ]-+----------- collname | es collnamespace | 2200 collowner | 10 collprovider | c collencoding | 6 collcollate | es_ES.utf8 collctype | es_ES.utf8 collversion | Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
"Daniel Verite" <daniel@manitou-mail.org> writes: > I can reproduce this by creating a new locale *after* starting > PostgreSQL and trying to use it before a restart. That is interesting. I think it must mean that glibc's setlocale() and newlocale() maintain some kind of internal cache about available locales ... and there's no logic to flush it if /usr/share/locale changes. The individual backends are probably inheriting the cache state via fork from the postmaster. regards, tom lane
Thank you :) I'll try restarting postgresql during our next maintenance window and report back.
--cnemelka
On Mon, Sep 9, 2019 at 3:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Daniel Verite" <daniel@manitou-mail.org> writes:
> I can reproduce this by creating a new locale *after* starting
> PostgreSQL and trying to use it before a restart.
That is interesting. I think it must mean that glibc's setlocale()
and newlocale() maintain some kind of internal cache about available
locales ... and there's no logic to flush it if /usr/share/locale
changes. The individual backends are probably inheriting the cache
state via fork from the postmaster.
regards, tom lane