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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Erwin Brandstetter <a9006241 (AT) unet (DOT) univie.ac.at> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster