Thread: LC_MESSAGES = 'de_AT' screws restoring from dump.

Hello!

I have posted the following a week ago, but it was stalled as I was not
subscribed to pgsql-bugs. As it has not entered the list yet, I repost
after subsribing, assuming my mail has been eaten by the spam-filters.


I ported a PostgreSQL database to a new server. Dump from old server
shoulod work fine, but did not at first ..

SETUP:
======

Old server, debian Woody with postgresql backport:
-----------
test=# select version();
PostgreSQL 7.4.1 on i386-pc-linux-gnu, compiled by GCC 2.95.4

Locale = 'C'


New server, debian Sarge:
-----------
test=# select version();
PostgreSQL 7.4.2 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc
(GCC) 3.3.3 (Debian 20040401)

Locale = de_AT

Both servers:
-------------
client_encoding = LATIN1
encoding = UNICODE
I work from a console logged in with puTTY.


Now the exact same dump file worked fine on the old server, but did not
on the new.
I was confused.
What i finally found (after recovering from desparation): lc_messages is
the problem !!


Demonstration:
==============

# While running with:
lc_messages = 'de_AT'
# on the new server i tried:

(...)
postgres@dbneu:~$ psql test -f ./e_schema.sql > /dev/null

(...)
psql:./e_schema.sql:157: FEHLER:  could not convert UTF-8 character
0x00fc to ISO8859-1
psql:./e_schema.sql:159: FEHLER:  Relation »naehe« existiert nicht

# Many more 'could not convert UTF-8 character 0x00fc to ISO8859-1' and
friends follow. Parts of the restore fail.

# The relevant part of the dump:
...
153
154 CREATE TABLE naehe (
155    naehe_id int4 PRIMARY KEY,
156    naehe text NOT NULL
157 ) WITHOUT OIDS;
158
...

# Several tables are being created earlier in this dump, only difference
here:
# implicit creation of primary key, which triggers a notice on restore..
# This turned out to be the problem.


SOLUTION:
=========

# After editing postgresql.conf:
lc_messages = 'C'

postgres@dbneu:~$ pg_ctl reload
# .. success
postgres@dbneu:~$ psql test -f ./e_schema.sql > /dev/null

psql:./e_schema.sql:157: NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index "naehe_pkey" for table "naehe"

# Works fine! NO errors at all! I did not change _anything_ but
lc_messages = 'C'.


BUG?
====

There seems to be something wrong with German error messages?
I don't understand the details though.
Maybe a Debian problem? Should i file a bug there?



Thanx for considering.
Pleace cc me on replies.


Regards
Erwin Brandstetter

Erwin Brandstetter <a9006241@unet.univie.ac.at> writes:
> # While running with:
> lc_messages = 'de_AT'
> encoding = UNICODE

> psql:./e_schema.sql:157: FEHLER:  could not convert UTF-8 character
> 0x00fc to ISO8859-1
> psql:./e_schema.sql:159: FEHLER:  Relation »naehe« existiert nicht

> There seems to be something wrong with German error messages?

I suspect that de_AT on your machine implies a character set encoding
other than Unicode (likely 8859-something).  So strerror() returns
a message that is in 8859-something, but the backend assumes that all
strings inside it are in Unicode, and tries to convert based on that
assumption.  You need to use a locale setting that conforms to the
database encoding you've selected.  It might be called de_AT.utf8
or some such.

It's a real pain in the neck that Postgres can't detect locale settings
that are incompatible with the database encoding.  I don't know any
portable way to find that out, though.

            regards, tom lane