client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

From: Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade
Date: 2018-04-16 15:16:40
Message-ID: CAODZiv7K+AaJSPk=uqmNa7UQ2va7z_1d_KgVeZzEeqDNvgBn9g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Running into an issue with helping a client upgrade from 8.3 to 10 (yes, I
know, please keep the out of support comments to a minimum, thanks :).

The old database was in SQL_ASCII and it needs to stay that way for now
unfortunately. The dump and restore itself works fine, but we're now
running into issues with some data returning encoding errors unless we
specifically set the client_encoding value to SQL_ASCII.

Looking at the 8.3 database, it has the client_encoding value set to UTF8
and queries seem to work fine. Is this just a bug in the old 8.3 not
enforcing encoding properly?

The other thing I noticed on the 10 instance was that, while the LOCALE was
set to SQL_ASCII, the COLLATE and CTYPE values for the restored databases
were en_US.UTF-8. Could this be having an affect? Is there any way to see
what these values were on the old 8.3 database? The pg_database catalog
does not have these values stored back then.

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-04-16 15:37:42 Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade
Previous Message Jack Gao 2018-04-16 09:32:09 Re: SQL statement in an error report for deferred constraint violation.