Issues with inconsistent COLLATION installation

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Issues with inconsistent COLLATION installation

Cory Nemelka
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


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


Anyone having similar issues or know of the solution?

TIA,
--cnemelka
Reply | Threaded
Open this post in threaded view
|

Re: Issues with inconsistent COLLATION installation

Cory Nemelka
We have already run pg_import_system_collations('pg_catalog')
--cnemelka


On Mon, Sep 9, 2019 at 12:43 PM Cory Nemelka <[hidden email]> 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.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


Anyone having similar issues or know of the solution?

TIA,
--cnemelka
Reply | Threaded
Open this post in threaded view
|

Re: Issues with inconsistent COLLATION installation

Cory Nemelka
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           │
└───────────┴──────────┴──────────┴─────────────┴─────────────┴───────────────────────┘

--cnemelka


On Mon, Sep 9, 2019 at 12:45 PM Cory Nemelka <[hidden email]> wrote:
We have already run pg_import_system_collations('pg_catalog')
--cnemelka


On Mon, Sep 9, 2019 at 12:43 PM Cory Nemelka <[hidden email]> 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.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


Anyone having similar issues or know of the solution?

TIA,
--cnemelka
Reply | Threaded
Open this post in threaded view
|

Re: Issues with inconsistent COLLATION installation

Tom Lane-2
Cory Nemelka <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Issues with inconsistent COLLATION installation

Daniel Verite
In reply to this post by Cory Nemelka
        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


Reply | Threaded
Open this post in threaded view
|

Re: Issues with inconsistent COLLATION installation

Tom Lane-2
"Daniel Verite" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Issues with inconsistent COLLATION installation

Cory Nemelka
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 <[hidden email]> wrote:
"Daniel Verite" <[hidden email]> 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