Re: SQL_ASCII support (or lack thereof)

Hi Dave,

Will pick this up - https://redmine.postgresql.org/issues/3289

Thanks and Regards,
Aditya Toshniwal
Software Engineer | EnterpriseDB Software Solutions | Pune
"Don't Complain about Heat, Plant a tree"

On Fri, May 18, 2018 at 1:25 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> 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(dot)ascentgl(at)gmail(dot)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 <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
>>>
>>
>>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>