Re: SQL_ASCII support (or lack thereof) - Mailing list pgadmin-support

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@pgadmin.org> wrote:

On Thu, May 17, 2018 at 3:06 PM, richard coleman <rcoleman.ascentgl@gmail.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