Re: Unable to restore dump due to client encoding issues -- or, when is SQL_ASCII really UTF8

In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Bill Moran <wmoran(at)collaborativefusion(dot)com> writes:
> > In response to Michael Fuhr <mike(at)fuhr(dot)org>:
> >>> Connecting to the database and issuing "show client_encoding" shows that
> >>> the database is indeed set to SQL_ASCII.
> >>
> >> client_encoding doesn't show the database encoding, it shows the
> >> client encoding; execute "show server_encoding" to see the database
> >> encoding.
>
> > The database was, indeed, UTF8, which is the default on newer Postgres.
>
> No, it's not necessarily the default --- the default is the encoding
> used by whatever locale you initdb'd in.

Ok, but Postgres' idea of what locale translates to what encoding has
changed from 8.1.4 to 8.1.8 ... or FreeBSD changed something about the
way it uses locales ... or something ...

> But what I find odd about the
> above is that client_encoding should default to equal server_encoding
> unless something on the client side specifically overrides it. The
> above behavior suggests that you've got a .psqlrc or PGCLIENTENCODING
> environment variable or some such that is forcing client_encoding to
> SQL_ASCII when the server encoding is something different. That strikes
> me as a pretty bad practice; there is use for forcing client_encoding to
> something specific, but forcing it to SQL_ASCII seems useless and
> possibly dangerous.

Well, the explanation for that got snipped -- the file created by pg_dump
had a set client_encoding statement at the beginning of it, which seems
logical to me: the database this was dumped from was SQL_ASCII, so the
restore process should attempt to replicate that. I'm chalking this up
to "Bill didn't notice that the new databases were getting created with a
different encoding."

--
Bill Moran
Collaborative Fusion Inc.