EDB team; I forget which one of you was going to look into the existing ticket about issues with SQL_ASCII handling, but here's some more information that may help. Please take a look ASAP.
Thanks. On Thu, May 17, 2018 at 5:53 PM, richard coleman < rcoleman.ascen...@gmail.com> wrote: > 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 <dp...@pgadmin.org> wrote: > >> >> >> On Thu, May 17, 2018 at 3:06 PM, richard coleman < >> rcoleman.ascen...@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 >> > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
