Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

On Mon, Apr 16, 2018 at 12:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com> writes:
> > On Mon, Apr 16, 2018 at 12:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> This is not a great idea, no. You could be getting strange misbehaviors
> >> in e.g. string comparison, because strcoll() will expect UTF8 data and
> >> will likely not cope well with data that isn't valid in that encoding.
>
> > And pg_controldata was able to show that the CTYPE and COLLATE were UTF8
> on
> > the old system. If that's the case, do you still think it's a good idea
> to
> > set the COLLATE and CTYPE to "C"?
>
> Well, if the customer's been happy with the behavior of the system so far,
> maybe it's all right. But this is sure the first thing I'd look at if
> there are any gripes about its behavior with non-UTF8 strings. I'd be
> especially worried about this if you try to migrate the database to any
> new platform, as it's a bet about the behavior of libc not PG itself.
>
> regards, tom lane
>

So playing around with the "client_encoding" configuration option in
postgresql.conf. According to the docs, setting this should set the default
encoding for any client that connects, right?

https://www.postgresql.org/docs/10/static/runtime-config-
client.html#GUC-CLIENT-ENCODING

That description also seems misleading in saying that the default client
encoding is what the database encoding is as well. At least as far as psql
is concerned, right? I've tried setting that value in postgresql.conf but
psql keeps setting it to UTF8 when I connect to the SQL_ASCII database
unless I then specifically run "set client_encoding = 'SQL_ASCII';"

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com