Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database.

Hi Hackers,

PFA updated patch.

On Thu, Jun 7, 2018 at 4:41 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> Hi
>
> On Thu, Jun 7, 2018 at 12:05 PM, Aditya Toshniwal <aditya.toshniwal@
> enterprisedb.com> wrote:
>
>> Hi Dave,
>>
>>
>> On Thu, Jun 7, 2018 at 4:07 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>>> Hi
>>>
>>> On Wed, Jun 6, 2018 at 2:02 PM, Aditya Toshniwal <
>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>
>>>> Hi Hackers,
>>>>
>>>> PFA updated patch as the previous one was not working as expected. I
>>>> have tried to make it similar to that of pgAdmin3 and you do not need to
>>>> change client_encoding as it is set now based on server encoding. It works
>>>> fine with "view data" also.
>>>>
>>>
>>> - In connection.py, at ~409, shouldn't we set the client_encoding to
>>> SQL_ASCII? Otherwise it could be overridden with something unexpected if
>>> the client has PGCLIENTENCODING set for example.
>>>
>> Yeah I agree, it would be better to add. Will add the change.
>>
>>>
>>> - With or without that change, I get the following test failure on macOS
>>> with Python 2.7.10:
>>>
>> It works fine on my machine with Python 2.7 and macOS. Could you please
>> let me know the Postgres DB version also.
>>
>
> PostgreSQL 9.4.10 on x86_64-apple-darwin, compiled by
> i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build
> 5658) (LLVM build 2336.11.00), 64-bit
>
>
>> Will test on few more machines.
>>
>>>
>>> ======================================================================
>>> ERROR: runTest (pgadmin.tools.sqleditor.tests
>>> .test_encoding_charset.TestEncodingCharset)
>>> With Encoding SQL_ASCII
>>> ----------------------------------------------------------------------
>>> Traceback (most recent call last):
>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py",
>>> line 86, in runTest
>>> response = self.tester.get(url)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
>>> line 830, in get
>>> return self.open(*args, **kw)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/testing.py",
>>> line 127, in open
>>> follow_redirects=follow_redirects)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
>>> line 803, in open
>>> response = self.run_wsgi_app(environ, buffered=buffered)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
>>> line 716, in run_wsgi_app
>>> rv = run_wsgi_app(self.application, environ, buffered=buffered)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
>>> line 923, in run_wsgi_app
>>> app_rv = app(environ, start_response)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>> line 1997, in __call__
>>> return self.wsgi_app(environ, start_response)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>> line 1985, in wsgi_app
>>> response = self.handle_exception(e)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>> line 1540, in handle_exception
>>> reraise(exc_type, exc_value, tb)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>> line 1982, in wsgi_app
>>> response = self.full_dispatch_request()
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>> line 1614, in full_dispatch_request
>>> rv = self.handle_user_exception(e)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>> line 1517, in handle_user_exception
>>> reraise(exc_type, exc_value, tb)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>> line 1612, in full_dispatch_request
>>> rv = self.dispatch_request()
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>> line 1598, in dispatch_request
>>> return self.view_functions[rule.endpoint](**req.view_args)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask_login.py",
>>> line 792, in decorated_view
>>> return func(*args, **kwargs)
>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/__init__.py",
>>> line 576, in poll
>>> 'oids': oids
>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/ajax.py", line 61,
>>> in make_json_response
>>> separators=(',', ':')),
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/__init__.py",
>>> line 399, in dumps
>>> **kw).encode(obj)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/encoder.py",
>>> line 291, in encode
>>> chunks = self.iterencode(o, _one_shot=True)
>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/encoder.py",
>>> line 373, in iterencode
>>> return _iterencode(o, 0)
>>> UnicodeDecodeError: 'utf8' codec can't decode byte 0xad in position 0:
>>> invalid start byte
>>>
>>> ----------------------------------------------------------------------
>>> Ran 317 tests in 30.692s
>>>
>>> FAILED (errors=1, skipped=21)
>>>
>>
This is fixed. There was a problem with json dumps. Now, json dumps will be
done based on connection encoding.

>
>>>
>>>
>>>> The only problem is, I cannot find equivalent codec for wxConvLibc in
>>>> python. The closest one I could find is raw_unicode_escape. So, in a
>>>> SQL_ASCII database, non ASCII characters may differ in pgAdmin4 and
>>>> pgAdmin3, but it will display results.
>>>>
>>>
>>> Yeah, I think that's fine. For the small number of people with SQL_ASCII
>>> databases, seeing escaped characters is better than nothing.
>>>
>>>
>>>>
>>>>
>>>> Dave,
>>>> You need to add "E" before the string to be inserted, otherwise \x will
>>>> be considered as a plain string.
>>>> INSERT INTO sql_ascii (data) VALUES (E'[Invalid UTF-8] Blob:
>>>> \xf4\xa5\xa3\xa5');
>>>>
>>>
>>> Yeah, sorry - I copied the wrong version of the query :-(
>>>
>>>
>>>>
>>>>
>>>> Kindly review.
>>>>
>>>> Thanks and Regards,
>>>> Aditya Toshniwal
>>>> Software Engineer | EnterpriseDB Software Solutions | Pune
>>>> "Don't Complain about Heat, Plant a tree"
>>>>
>>>> On Tue, Jun 5, 2018 at 6:42 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> On Tue, Jun 5, 2018 at 2:03 PM, Aditya Toshniwal <
>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>> On Tue, Jun 5, 2018 at 6:25 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Tue, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal <
>>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>>
>>>>>>>> Hi Dave,
>>>>>>>>
>>>>>>>> The problem of SQL ASCII is solved with the patch, and not related
>>>>>>>> to setting the client encoding of the sql window.
>>>>>>>>
>>>>>>>
>>>>>>> No it's not. It doesn't work for me as I said (and showed the
>>>>>>> example of).
>>>>>>>
>>>>>>
>>>>>> After setting the client_encoding to SQL_ASCII you got the output.
>>>>>> Previously, it used to fail in the back end itself because python encoding
>>>>>> failure. That is fixed.
>>>>>> The error ERROR: invalid byte sequence for encoding "UTF8": 0x80 is
>>>>>> thrown by postgres and not python or pgAdmin4. You will get the same error
>>>>>> even if you
>>>>>> connect from psql.
>>>>>>
>>>>>
>>>>> Sure - but that is not a fix. You have no way of running the SET
>>>>> command if you're using "view data" - and in the query tool, users just
>>>>> expect it to work (as it did in pgAdmin 3).
>>>>>
>>>>>
>>>>>>
>>>>>>>
>>>>>>>> I can see there is no SET call in pgAdmin3 for client_encoding. I
>>>>>>>> can remove the SET client_encoding='UNICODE'; that will solve the
>>>>>>>> problem. But, can you please let me know why that was added.
>>>>>>>>
>>>>>>>
>>>>>>> There is, but it's inside an API call (PQsetClientEncoding):
>>>>>>>
>>>>>>> 300
>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l300>
>>>>>>> wxLogInfo(wxT("Setting client_encoding to '%s'")
>>>>>>> , encoding.c_str());
>>>>>>> 301
>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l301>
>>>>>>> if (PQsetClientEncoding(conn, encoding.ToAscii()))
>>>>>>> 302
>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l302>
>>>>>>> {
>>>>>>> 303
>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l303>
>>>>>>> wxLogError(wxT("%s"), GetLastError().c_str());
>>>>>>> 304
>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l304>
>>>>>>> }
>>>>>>> 305
>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l305>
>>>>>>>
>>>>>>> Oops ! Missed that. Apologies.
>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>
>>>>>>>> Will remove the set call and will send you the updated patch if
>>>>>>>> everything works fine.
>>>>>>>>
>>>>>>>
>>>>>>> No, we need to ensure the client encoding is set correctly. It just
>>>>>>> needs to be set to SQL_ASCII if it's a SQL_ASCII database (I believe).
>>>>>>>
>>>>>>>
>>>>>> Need to rework on the initialise method. Will come with an updated.
>>>>>> patch. Sorry for trouble.
>>>>>>
>>>>>>>
>>>>>>>>
>>>>>>>> On Tue, Jun 5, 2018 at 6:05 PM, Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hi
>>>>>>>>>
>>>>>>>>> On Tue, Jun 5, 2018 at 1:21 PM, Aditya Toshniwal <
>>>>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>>>>
>>>>>>>>>> Hi Dave,
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Tue, Jun 5, 2018 at 4:56 PM, Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>>>> wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi
>>>>>>>>>>>
>>>>>>>>>>> On Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniwal <
>>>>>>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>>>>>>
>>>>>>>>>>>> Hi Hackers,
>>>>>>>>>>>>
>>>>>>>>>>>> PFA updated patch. The sqleditor change is sent separately and
>>>>>>>>>>>> removed from current patch as suggested.
>>>>>>>>>>>> The test cases were running fine when the module was specified
>>>>>>>>>>>> using --pkg but were failing in complete run. Fixed that.
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> I did a quick test by creating a SQL_ASCII database containing a
>>>>>>>>>>> simple table:
>>>>>>>>>>>
>>>>>>>>>>> CREATE TABLE sql_ascii (id serial primary key, data text);
>>>>>>>>>>>
>>>>>>>>>>> And then populated it with data:
>>>>>>>>>>>
>>>>>>>>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c
>>>>>>>>>>> "INSERT INTO sql_acsii (data) VALUES ('[Windows-1252] Euro: \x80 Double
>>>>>>>>>>> dagger: \x87');"
>>>>>>>>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c
>>>>>>>>>>> "INSERT INTO sql_ascii (data) VALUES ('[Latin-1] Yen: \xa5 Half:
>>>>>>>>>>> \xbd');"
>>>>>>>>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c
>>>>>>>>>>> "INSERT INTO sql_ascii (data) VALUES ('[Japanese] Ship: \xe8\x88\xb9');"
>>>>>>>>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c
>>>>>>>>>>> "INSERT INTO sql_ascii (data) VALUES ('[Invalid UTF-8] Blob:
>>>>>>>>>>> \xf4\xa5\xa3\xa5');"
>>>>>>>>>>>
>>>>>>>>>>> I then right-clicked the table in the treeview, and selected the
>>>>>>>>>>> option to view all rows, and immediately saw an error:
>>>>>>>>>>>
>>>>>>>>>>> 2018-06-05 12:23:27,319: SQL pgadmin: Execute (async) for
>>>>>>>>>>> server #1 - CONN:1187535 (Query-id: 8522474):
>>>>>>>>>>> SELECT * FROM public.sql_ascii
>>>>>>>>>>> ORDER BY id ASC
>>>>>>>>>>> 2018-06-05 12:23:27,320: ERROR pgadmin: Failed to execute query
>>>>>>>>>>> (execute_async) for the server #1 - CONN:1187535(Query-id: 8522474):
>>>>>>>>>>> Error Message:ERROR: invalid byte sequence for encoding "UTF8":
>>>>>>>>>>> 0x80
>>>>>>>>>>> SQL state: 22021
>>>>>>>>>>>
>>>>>>>>>>> Running "SELECT * FROM sql_ascii" in the query tool resulted in
>>>>>>>>>>> the same error, however, if I ran "SET client_encoding = 'SQL_ASCII';"
>>>>>>>>>>> first, I do see results.
>>>>>>>>>>>
>>>>>>>>>>> I have confirmed that I've restarted the server after applying
>>>>>>>>>>> the patch.
>>>>>>>>>>>
>>>>>>>>>>> What am I missing? Why don't we just set the client_encoding to
>>>>>>>>>>> SQL_ASCII if it's a SQL_ASCII database?
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> It is by default same as the server encoding. But, the following
>>>>>>>>>> existing code in web/pgadmin/utils/driver/psycopg2/connection.py
>>>>>>>>>> makes the client_encoding as UNICODE for every connection. I am
>>>>>>>>>> not sure it should be removed.
>>>>>>>>>>
>>>>>>>>>> status = _execute(cur, "SET DateStyle=ISO;"
>>>>>>>>>>
>>>>>>>>>> "SET client_min_messages=notice;"
>>>>>>>>>>
>>>>>>>>>> "SET bytea_output=escape;"
>>>>>>>>>>
>>>>>>>>>> "SET client_encoding='UNICODE';")
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> It was probably before you joined, but I have said a number of
>>>>>>>>> times that pgAdmin 3 handled this differently and that maybe we should do
>>>>>>>>> it the same way here. See https://git.postgresql.org
>>>>>>>>> /gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp, in the
>>>>>>>>> pgConn::Initialize() function.
>>>>>>>>>
>>>>>>>>> Either way, your patch isn't working for me.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Note that this testing was on Python 2.7.10 on MacOS.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> Kindly review.
>>>>>>>>>>>>
>>>>>>>>>>>> Thanks and Regards,
>>>>>>>>>>>> Aditya Toshniwal
>>>>>>>>>>>> Software Engineer | EnterpriseDB Software Solutions | Pune
>>>>>>>>>>>> "Don't Complain about Heat, Plant a tree"
>>>>>>>>>>>>
>>>>>>>>>>>> On Tue, Jun 5, 2018 at 10:15 AM, Aditya Toshniwal <
>>>>>>>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>>>>>>>
>>>>>>>>>>>>> Hi
>>>>>>>>>>>>>
>>>>>>>>>>>>> On Tue, Jun 5, 2018 at 1:08 AM, Joao De Almeida Pereira <
>>>>>>>>>>>>> jdealmeidapereira(at)pivotal(dot)io> wrote:
>>>>>>>>>>>>>
>>>>>>>>>>>>>> Hello Aditya,
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> There is no change related to notifications in this patch.
>>>>>>>>>>>>>>> The below code is minor fix related to connection status of
>>>>>>>>>>>>>>> sql editor. Can you please share the code snippet if it is not the below.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> - # Check for the asynchronous notifies statements.
>>>>>>>>>>>>>>> - conn.check_notifies(True)
>>>>>>>>>>>>>>> - notifies = conn.get_notifies()
>>>>>>>>>>>>>>> + if status is not None:
>>>>>>>>>>>>>>> + # Check for the asynchronous notifies
>>>>>>>>>>>>>>> statements.
>>>>>>>>>>>>>>> + conn.check_notifies(True)
>>>>>>>>>>>>>>> + notifies = conn.get_notifies()
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>> This is a minor fix, but is it related to querying SQL_ASCII
>>>>>>>>>>>>>> database?
>>>>>>>>>>>>>>
>>>>>>>>>>>>> No its not. It is something I found when I was working on
>>>>>>>>>>>>> SQL_ASCII related changes.
>>>>>>>>>>>>> Well then, will send a separate patch for it.
>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Thanks
>>>>>>>>>>>>>> Victoria && Joao
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> 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
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> 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
>