Hello,

today I ran into the "encoding problem". Migrating from Postgres 8 to
Postgres 8.3 on a new Debian on another server, psql refused to import
my LATIN1 databases, because the server has UTF8 set as locale.

As I wanted to migrate to UTF8 with that specific database anyway, I
edited my dump and went on with it, of course not after wasting a lot of
time on research (because it was not mentioned, or at least not
highlighted in the migration guide).

So before I put the PostgreSQL people on the "black list", I'd like to
try to understand first. What is it good for, why is it necessary? I
don't see a "hard" reason for it, as certainly it hasn't been necessary
up to Postgres 8. After all, what should be the problem of a RDBMS to
handle different encoding standards in different databases, and what
should be the problem of "conflicting" with the server? If I couldn't
use UTF-8 in my application, well, I would switch the client encoding to
LATIN1, so he would need to do conversion anyway. I just don't get the
f... necessity of why it should be so inflexible, especially as it has
not been necessary before.

Even so, I would at least wish they made it clearer in their migration
guide, and provided more comfortable tools to migrate.

Maybe someone can help me to understand why the behaviour has been changed.

Hadanite Marasek wrote:
What exactly do you mean by "Postgres 8"?

If you did everything correctly, that would mean that you had some
non-Latin1 characters in your Latin1 database.

Older versions of PostgreSQL have been less strict about verifying
that the data in the database are properly encoded. This is an
ongoing effort.

Not that I am particularly afraid of your black list, but I aknowledge
the willingness to try to understand. It is deplorable that your
posting has such an annoyed subtext.

What exactly do you mean by "it"? UTF-8? Error messages?

Ok, I admit that you lost me somewhere in that longer paragraph.

What behaviour of PostgreSQL are you complaining about? That it makes
sure that strings are stored in the database encoding?

Is it that you want to store random byte sequences in the database and
avoid any encoding checks? If that is the case, why don't you either
store these data as binary fields or use SQL_ASCII as database encoding,
which is particularly designed for people who want their database to act
in garbage in - garbage out mode?

If you want your special characters to come out "right" on different
platforms with different encodings, your database has to understand
what each character means. For that you will have to put up with some
fussiness on the database's side.

If you are referring to stricter encoding checks in recent PostgreSQL
versions, then the answer is that the old, laxer behaviour was a bug
that has been fixed. If you rely on the upward compatibility of buggy
behaviour, you're in for an unpleasant surprise every now and then.

Yours,
Laurenz Albe

Laurenz Albe schrieb:
PostgreSQL 8.0.0

That was not the cause, I'm talking about this:

http://www.ashtech.net/~syntax/blog/...ng-Issues.html

Well, I found it deplorable to deal with that problem, and yes, it
annoyed me a LOT.
I went strictly after the manual presented in
http://www.postgresql.org/docs/8.3/i...P-DUMP-RESTORE

Do you see something like "use vim to change the create statements" in
there?

No, that it is not possible to create databases which have a different
encoding than the server's locale.

Hadanite Marasek wrote:
Ouch.

Ah, you want to create databases with different encodings in one
database cluster, right?

In PostgreSQL 8.3, you can do that if you create your cluster
with the C locale.

From PostgreSQL 8.4 on, you can set LC_COLLATE and LC_CTYPE per database
in CREATE DATABASE if you use template0 as database template.

Both should solve your problem without trouble.
Just pre-create the database and restore the dump into it.

I understand your gripe in this case.
It is only mentioned in the release notes, but I guess it would have
deserved a mention in chapter 15.4.

Nobody is perfect.

But your problem should be addressed with the above, right?

Yours,
Laurenz Albe

Laurenz Albe schrieb:
Whats Ouch?

Yes, but maybe to give you more details: I run PostgreSQL on a server
with other services, as it serves as a storage for two internal
webapplications with little concurrent access. So I didn't see that as
an option.
In the end, I changed the dump as well as the one application that still
ran on Latin1 (wanted to do that anyway).

Well, I solved it the other way, but maybe it helps someone else. In the
end, I don't want to miss UTF-8 anymore.

Hadanite Marasek wrote:
"Ouch" meaning it hurts to hear that somebody runs 8.0.0 when 8.0.23
is in the making. You don't care much about data corruption and
similar problems, right?

You don't have to change anything on the server, all you have to do is
use the --locale option to initdb. Easy as that.
Sorry if I was unclear.

Nor do I.

Yours,
Laurenz Albe

Hadanite Marasek schrieb:
You are SO RIGHT, I am annoyed too about the problem you can not restore your dump without very detailed
investigation.

It is a big waste of time for me.

/Str.