Issues with inconsistent COLLATION installation

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:

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 <cnemelka@gmail.com> wrote:

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: