Re: [SOLVED] Problems with a custom LOCALE - Mailing list pgsql-general

On Sun, December 16, 2012 15:13, Tom Lane wrote:
> "James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>>>> PG::Error: ERROR:  encoding "UTF8" does not match locale
>>>> "en_CA@yyyy-mmm-dd.UTF-8"
>>>> DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1".
>
>> This is what I see on the host running postgresql-9.2
>> # LC_ALL=en_CA@yyyy-mmm-dd.utf8 locale charmap
>> UTF-8
>
>> Running locale against the base en_CA@yyyy-mmm-dd on the PG host
>> shows
>> this.
>> LC_ALL=en_CA@yyyy-mmm-dd locale charmap
>> ISO-8859-1
>
> You're showing us three different spellings of the locale name above.
> Are you really sure they're all equivalent?
>
> Beyond that, you probably need to find a locale guru.  I see no reason
> to think there is anything wrong with the Postgres code for this, and
> every reason to think there's something wrong with your locale
> definition.  But I don't know enough about custom locales to help you
> identify exactly what.
>
>             regards, tom lane
>

Well, I did eventually track this down and fix it.  It was a number of
issues which all sort of came together to drive me insane.

First, the RDBMS was a remote host.  On CentOS/RHEL ssh is configured
to export the local session's LC environment to remote hosts.
Problem, en_CA@yyyy-mmm-dd did not exist on the remote host.  Running
initdb in a ssh shell with the locale set to a non-existent local
locale probably affected the database structure in some obscure way,
although the postgresql.conf file said 'en_US.UTF-8'.  However,
perhaps the config file is ignored by the init.d script on RHEL in
this respect as well.

Of course, postgresql.conf does not yet exist when one runs initdb.
Hmm.  I will leave that one for the existentialists among us.

In any case, installing the custom locale on the remote host did not
solve the problem because when localedef could not find the UTF-8
character map it used ISO-8859-1 instead and kept that piece of
information to itself because, I did not specify the -v option.  Of
course, if you do specify the -v option and there are any warnings
then the existing character map is not replaced even when it does find
the utf-8 character map. That is, unless you also specify the -c
option.

And finally, you need to archive the base character map (.utf8) and
then create aliases for the rest (.utf-8, UTF8 and UTF-8).  And the
way one does that is to use the self-explanatory --no-archive option
together with the -c option on the aliases but use --replace option on
the localedef call to create the archived map.

Really, localedef is perhaps the most Byzantine and under documented
single function utility that I have run across since I stopped writing
assembler.

Anyway, after rebuilding the remote 9.2 database whilst having the
remote session locale set to en_US.UTF-8, resetting the remote and
local host's locale databases and aliases for en_CA@yyyy-mmm-dd, and
then trying everything once more on the development host I ran into
this error:

PG::Error: ERROR:  new encoding (UTF8) is incompatible with the
encoding of the template database (LATIN1)
HINT:  Use the same encoding as in the template database, or use
template0 as template.
: CREATE DATABASE "hll_th_forex_development" ENCODING = 'UTF-8'

So, I now must specify a template in the database connection, where I
did not need to before. Furthermore, the template I must use is
template0 because regardless of what locale initdb is run in template1
evidently is always created with LATIN1 encoding.  At lease if there
is a way to force template1 to utf8 during initdb I now lack the
energy to discover it.

However, specifying template0 in the db connection does allow the
remote db creation to succeed.

--
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3