Re: SQL_ASCII support (or lack thereof)

Dave,

Thanks for getting back to me on this issue. If this is the case then it
would seem that using python for pgAdmin4 development was a poor choice
considering the fact that PostgreSQL 10 supports more than two dozen types
of encoding, only one of which is UTF8.

In my case I have a SQL_ASCII database that backs a windows desktop
application (among other things). The users have the ability to copy/paste
into various text fields (say from email or Microsoft Word). In the
immediate case pgAdmin4 is apparently complaining about 0xc9 (É) and 0x4f
(O). The *solution* if I want to use pgAdmin4 is to find and
convert/remove the characters that pgAdmin4 is complaining about. As I had
mentioned previously, pgAdmin3 doesn't have this issue (unfortunately it
doesn't fully support postgresql 10) neither does the .Net application that
makes use of this postgresql database. As this is a production database,
it isn't practical to convert the database to UTF8, and then rewrite the
applications (there are several) to scrub user input to limit it to only
UTF8 characters. Ideally pgAdmin4 should support all of the encoding
schemes that postgresql 10 does.

Here's the DDL for my database:

CREATE DATABASE tms_production
> WITH
> OWNER = local_user
> ENCODING = 'SQL_ASCII'
> LC_COLLATE = 'English_United States.1252'
> LC_CTYPE = 'English_United States.1252'
> TABLESPACE = pg_default
> CONNECTION LIMIT = -1;
> ALTER DATABASE tms_production
> SET default_transaction_read_only TO off;
> ALTER DATABASE tms_production
> SET client_encoding TO SQL_ASCII;
> ALTER DATABASE tms_production
> SET standard_conforming_strings TO off;

To replicate the issue;

- create a table with a text field.
- paste some valid ASCII.1252, but invalid UTF8 data into it.
- try to do something (ex:SELECT * FROM <your_table> ;) in pgAdmin4 with
that table
- watch pgAdmin4 throw errors.

rik.

On Thu, May 17, 2018 at 10:17 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

>
>
> On Thu, May 17, 2018 at 3:06 PM, richard coleman <
> rcoleman(dot)ascentgl(at)gmail(dot)com> wrote:
>
>> Why is pgAdmin 4 so hostile to SQL_ASCII databases?
>>
>> We have several production databases dating back to 9.1 that are
>> SQL_ASCII encoding but in pgAdmin4 I am constantly having to *clean up*
>> non *UTF8* data. The same data works just fine in the pgAdmin3 series.
>>
>> Running the same query in psql yields the expected results, but in this
>> case I get:
>>
>> "ERROR: invalid byte sequence for encoding "UTF8": 0xc9 0x4f
>> SQL state: 22021"
>>
>> in pgAdmin4.
>>
>> If I remove the offending characters then pgAdmin4 returns a result set.
>> The database is SQL_ASCII encoded, pgAdmin4 *shouldn't care* that there
>> are non UTF8 characters present.
>>
>
> pgAdmin doesn't - Python does. If you can give some examples, we may be
> able to figure out the issue and work around it.
>
> It's worth noting though that it's usually a bad idea to use SQL_ASCII
> (that's not intended as an excuse, just as some advice).
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>