Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fTLNR-0004yO-67 for pgadmin-hackers@arkaria.postgresql.org; Thu, 14 Jun 2018 06:04:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fTLNP-00007M-6I for pgadmin-hackers@arkaria.postgresql.org; Thu, 14 Jun 2018 06:04:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fTLNO-00007C-RM for pgadmin-hackers@lists.postgresql.org; Thu, 14 Jun 2018 06:04:55 +0000 Received: from mail-lf0-x244.google.com ([2a00:1450:4010:c07::244]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fTLNJ-0004O3-Eh for pgadmin-hackers@postgresql.org; Thu, 14 Jun 2018 06:04:54 +0000 Received: by mail-lf0-x244.google.com with SMTP id n3-v6so7461983lfe.12 for ; Wed, 13 Jun 2018 23:04:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=BMNqXS3p3/79vCvxlCpwUvSfEF+hQm3OzS3M04lciGs=; b=tNmE4i1P23/Hg6saU0C4nyH1Pmu77KKL1snRSDSv1IR0sZHaqAvrW2LvksCYVm4Epe Z2th3WtmEf8ORTCwoDNLk1UW0pmN+FsFg93gShgmM181lhzn1xqX+dVOWT5Xyp1IPt5d legupRnjK7vHVDsuvexDTsI/RBIMslFFIFLBtTMrQq000N7lE4ERmPwVnrJyhyyf0Oif bntxq9uDbgrrZSxkszkqIidbG5pmX8CUiAJUxm+vJDEOOLVTAryc5xxwJnPhY5iptPRZ 6lHIe7b0OoRtw2/n/wj9STXOh5UeFcqlVi2GH4rbaZ5aAuGVP61cuBKev7PQkJLP6bp3 Y1wQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=BMNqXS3p3/79vCvxlCpwUvSfEF+hQm3OzS3M04lciGs=; b=H2t8oO9eS5BfKkYXlvjl/9GS/Lr5Xrs9bTPDgDEIZ0cAzu9cmej5ASID3MUu4qC/jD NoLee9+5+Bvk4N7hnPlQJoOHGwwPyb79NrDEzf6uLDdfwo+lVZ5Sz0iGf/TZKSAiP+13 fmWNMJ0xr5bwThLkN9cxcTVMPu+CZKTh04irxr7B7kmQGEWDRagAYry2nYCdfQw00EgD Avy2vwNnisOv6dBDg39Q1mksI1loFsNqeVY42C8toaYmebLUvHR0+5KmxAAFPqnggZEr SCTiyCHMKCyRlSZbgwcYdeaM6QWqphMED1wXQy6LC6qV3C7fizE5E/9f8qZUuM4goqyl SEnQ== X-Gm-Message-State: APt69E2Fj8MEVuPQM5c671QB78pwq/eYellRWXL+uz33NKl3gIOtv1Np jIEFq/LMmoYJ6f8Fj/kKWgyGqKLoUkUGJdN9x5YEUA== X-Google-Smtp-Source: ADUXVKIttZ9ESyiXaPuTMjW3C6WTS5OEmN9v/z7bcHjbibhZw4XC+zsqaEiv3jTPHsgRPbWeZrAsrg7mEoja2icp92g= X-Received: by 2002:a19:dec2:: with SMTP id i63-v6mr4817412lfl.53.1528956288462; Wed, 13 Jun 2018 23:04:48 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a2e:7a02:0:0:0:0:0 with HTTP; Wed, 13 Jun 2018 23:04:47 -0700 (PDT) In-Reply-To: References: From: Aditya Toshniwal Date: Thu, 14 Jun 2018 11:34:47 +0530 Message-ID: Subject: Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database. To: Dave Page Cc: Joao De Almeida Pereira , pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000738de4056e93e086" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000738de4056e93e086 Content-Type: text/plain; charset="UTF-8" Hi Hackers, PFA updated patch. On Thu, Jun 7, 2018 at 4:41 PM, Dave Page wrote: > Hi > > On Thu, Jun 7, 2018 at 12:05 PM, Aditya Toshniwal enterprisedb.com> wrote: > >> Hi Dave, >> >> >> On Thu, Jun 7, 2018 at 4:07 PM, Dave Page wrote: >> >>> Hi >>> >>> On Wed, Jun 6, 2018 at 2:02 PM, Aditya Toshniwal < >>> aditya.toshniwal@enterprisedb.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 wrote: >>>> >>>>> Hi >>>>> >>>>> On Tue, Jun 5, 2018 at 2:03 PM, Aditya Toshniwal < >>>>> aditya.toshniwal@enterprisedb.com> wrote: >>>>> >>>>>> Hi >>>>>> >>>>>> On Tue, Jun 5, 2018 at 6:25 PM, Dave Page wrote: >>>>>> >>>>>>> >>>>>>> >>>>>>> On Tue, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal < >>>>>>> aditya.toshniwal@enterprisedb.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 >>>>>>> >>>>>>> wxLogInfo(wxT("Setting client_encoding to '%s'") >>>>>>> , encoding.c_str()); >>>>>>> 301 >>>>>>> >>>>>>> if (PQsetClientEncoding(conn, encoding.ToAscii())) >>>>>>> 302 >>>>>>> >>>>>>> { >>>>>>> 303 >>>>>>> >>>>>>> wxLogError(wxT("%s"), GetLastError().c_str()); >>>>>>> 304 >>>>>>> >>>>>>> } >>>>>>> 305 >>>>>>> >>>>>>> >>>>>>> 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 >>>>>>>> wrote: >>>>>>>> >>>>>>>>> Hi >>>>>>>>> >>>>>>>>> On Tue, Jun 5, 2018 at 1:21 PM, Aditya Toshniwal < >>>>>>>>> aditya.toshniwal@enterprisedb.com> wrote: >>>>>>>>> >>>>>>>>>> Hi Dave, >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Tue, Jun 5, 2018 at 4:56 PM, Dave Page >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> Hi >>>>>>>>>>> >>>>>>>>>>> On Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniwal < >>>>>>>>>>> aditya.toshniwal@enterprisedb.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.toshniwal@enterprisedb.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Hi >>>>>>>>>>>>> >>>>>>>>>>>>> On Tue, Jun 5, 2018 at 1:08 AM, Joao De Almeida Pereira < >>>>>>>>>>>>> jdealmeidapereira@pivotal.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 > --000000000000738de4056e93e086 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Hackers,

PFA updated patch.

On Thu, Jun 7, 2018 at 4:41 PM, Dave Page <= dpage@pgadmin.org> wrote:
<= div dir=3D"ltr">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@pgadmin.= org> wrote:
Hi

On Wed, = Jun 6, 2018 at 2:02 PM, Aditya Toshniwal <aditya.toshniwal= @enterprisedb.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 une= xpected if the client has PGCLIENTENCODING set for example.
Yeah I agree, it would be better to add. W= ill add the change.=C2=A0
=

- With or= without that change, I get the following test failure on macOS with Python= 2.7.10:
It works fine on m= y machine with Python 2.7 and macOS. Could you please let me know the Postg= res DB version also.

PostgreSQL 9.4.10 on x86_64-apple-darwin, compiled by i686-apple-d= arwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLV= M build 2336.11.00), 64-bit
=C2=A0
Will test on few more = machines.=C2=A0

=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
ERROR: runTest (pgadmin.tools.sqleditor.tests.test_encoding_charset.T= estEncodingCharset)
With Encoding SQL_ASCII
------= ----------------------------------------------------------------<= /div>
Traceback (most recent call last):
=C2=A0 = File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/tests= /test_encoding_charset.py", line 86, in runTest
=C2=A0 = =C2=A0 response =3D self.tester.get(url)
=C2=A0 File "/Users= /dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug= /test.py", line 830, in get
=C2=A0 =C2=A0 return self.open(*= args, **kw)
=C2=A0 File "/Users/dpage/.virtualenvs/pgad= min4/lib/python2.7/site-packages/flask/testing.py", line 127, in = open
=C2=A0 =C2=A0 follow_redirects=3Dfollow_redirects)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2= .7/site-packages/werkzeug/test.py", line 803, in open
= =C2=A0 =C2=A0 response =3D self.run_wsgi_app(environ, buffered=3Dbuffered)<= /div>
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/pyt= hon2.7/site-packages/werkzeug/test.py", line 716, in run_wsgi_app=
=C2=A0 =C2=A0 rv =3D run_wsgi_app(self.application, environ, buf= fered=3Dbuffered)
=C2=A0 File "/Users/dpage/.virtualenvs/pga= dmin4/lib/python2.7/site-packages/werkzeug/test.py", line 92= 3, in run_wsgi_app
=C2=A0 =C2=A0 app_rv =3D app(environ, start_re= sponse)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4= /lib/python2.7/site-packages/flask/app.py", line 1997, in __call_= _
=C2=A0 =C2=A0 return self.wsgi_app(environ, start_response)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python= 2.7/site-packages/flask/app.py", line 1985, in wsgi_app
=C2=A0 =C2=A0 response =3D self.handle_exception(e)
=C2=A0 File = "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packa= ges/flask/app.py", line 1540, in handle_exception
=C2=A0 =C2= =A0 reraise(exc_type, exc_value, tb)
=C2=A0 File "/Users/dpa= ge/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py= ", line 1982, in wsgi_app
=C2=A0 =C2=A0 response =3D self.fu= ll_dispatch_request()
=C2=A0 File "/Users/dpage/.virtualenvs= /pgadmin4/lib/python2.7/site-packages/flask/app.py", line 16= 14, in full_dispatch_request
=C2=A0 =C2=A0 rv =3D self.handle_use= r_exception(e)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", line 1517, in = handle_user_exception
=C2=A0 =C2=A0 reraise(exc_type, exc_value, = tb)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib= /python2.7/site-packages/flask/app.py", line 1612, in full_dispat= ch_request
=C2=A0 =C2=A0 rv =3D self.dispatch_request()
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/si= te-packages/flask/app.py", line 1598, in dispatch_request
=C2=A0 =C2=A0 return self.view_functions[rule.endpoint](**req.view_= args)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/l= ib/python2.7/site-packages/flask_login.py", line 792, in decorate= d_view
=C2=A0 =C2=A0 return func(*args, **kwargs)
=C2= =A0 File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/_= _init__.py", line 576, in poll
=C2=A0 =C2=A0 'oids&= #39;: oids
=C2=A0 File "/Users/dpage/git/pgadmin4/web/p= gadmin/utils/ajax.py", line 61, in make_json_response
=C2=A0= =C2=A0 separators=3D(',', ':')),
=C2=A0 File &qu= ot;/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages= /simplejson/__init__.py", line 399, in dumps
=C2=A0 =C2=A0 *= *kw).encode(obj)
=C2=A0 File "/Users/dpage/.virtualenvs/pga<= wbr>dmin4/lib/python2.7/site-packages/simplejson/encoder.py", lin= e 291, in encode
=C2=A0 =C2=A0 chunks =3D self.iterencode(o, _one= _shot=3DTrue)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/encoder.py", line 3= 73, in iterencode
=C2=A0 =C2=A0 return _iterencode(o, 0)
UnicodeDecodeError: 'utf8' codec can't decode byte 0xad in po= sition 0: invalid start byte

---------------------= -------------------------------------------------
Ran 3= 17 tests in 30.692s

FAILED (errors=3D1, skipped=3D= 21)

This is fixed. There was a problem with json dumps. Now, json dumps will = be done based on connection encoding.=C2=A0
<= div dir=3D"ltr">
=

=C2=A0
The only pro= blem is, I cannot find equivalent codec for wxConvLibc in python. The close= st 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 t= hat's fine. For the small number of people with SQL_ASCII databases, se= eing escaped characters is better than nothing.
=C2=A0

Dave,=C2=A0
You need= to add "E" before the string to be inserted, otherwise \x will b= e considered as a plain string.
= INSERT INTO sql_ascii (data) VALUES (E'[Invalid UTF-8]=C2=A0 Blob: \xf4= \xa5\xa3\xa5');

Yeah, sorry - I copied the wrong version of the query :-(
=C2=A0<= /div>


Kindly review.

Thanks and Regards,
Aditya Toshniwal
Software Engineer |=C2=A0Ente= rpriseDB Software Solutions |=C2=A0Pune=
"Don't Complain a= bout Heat, Plant a tree"

On Tue, Jun 5, 2018 at 6:42 PM, Dave Page <dpage@pgadmin.= org> wrote:
Hi

On Tue, Jun 5, 2018 at 2:03 PM, Aditya Toshniwal &= lt;a= ditya.toshniwal@enterprisedb.com> wrote:
Hi

On Tue, Jun 5, 2018 at 6:= 25 PM, Dave Page <dpage@pgadmin.org> wrote:


On Tue, Jun 5, 2018 at 1:49 = PM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.c= om> wrote:
Hi Dave,

The problem of SQL= ASCII is solved with the patch, and not related to setting the client enco= ding of the sql window.

No it= 's not. It doesn't work for me as I said (and showed the example of= ).

After set= ting the client_encoding to SQL_ASCII you got the output. Previously, it us= ed to fail in the back end itself because python encoding failure. That is = fixed.
The error=C2=A0ERROR:=C2=A0= invalid byte sequence for encoding "UTF8": 0x80=C2= =A0is thrown by postgres and not python or pgAdmin4. You will get the same = error even if you=C2=A0
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&= quot; - and in the query tool, users just expect it to work (as it did in p= gAdmin 3).
=C2=A0
=C2= =A0
I can see there is no SET call in pgAdmin3 for client_encoding.=C2=A0 I ca= n remove the=C2=A0SET client_encoding=3D'UNI= CODE'; that will solve the problem. Bu= t, can you please let me know why that was added.=C2=A0

There is, but it's inside an API call = (PQsetClientEncoding):

300 =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0wxLogInfo= (wxT("Setting=C2=A0client_encoding=C2=A0to=C2=A0'%s'"= ;),=C2=A0encoding.c_str());
301 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0if=C2=A0(PQsetClientEncoding(co= nn,=C2=A0encoding.ToAscii()))
302 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0{
303 =C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0wxLogE= rror(wxT("%s"),=C2=A0GetLastError().c_str());
304 =C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0}
305=

Oops ! Missed t= hat. Apologies.=C2=A0
=C2=A0
Will remove the set call and will send you the updated patch i= f 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).<= /div>
=C2=A0
=
Need to rework on the init= ialise method. Will come with an updated. patch. Sorry for trouble.=C2=A0
=
=

On Tue, Jun 5, 2018 at 6:05 PM, Dave Page &l= t;dpage@pgadmin.org<= /a>> wrote:
=
Hi

On Tue, Jun 5, 2018 at 1:21 PM, Aditya Toshniwal= <aditya.toshniwal@enterprisedb.com> wr= ote:
= Hi Dave,


On= Tue, Jun 5, 2018 at 4:56 PM, Dave Page <dpage@pgadmin.org> = wrote:
Hi

On Tue, Jun 5, 2018 at 9:50 A= M, Aditya Toshniwal <aditya.toshniwal@enterprisedb.co= m> wrote:
Hi Hackers,

PFA updated patch. The sq= leditor change is sent separately and removed from current patch as suggest= ed.
The test cases were running fine when the module was specifie= d using --pkg but were failing in complete run. Fixed that.

I did a quick test by creating a SQL_AS= CII database containing a simple table:

CREATE TAB= LE sql_ascii (id serial primary key, data text);

A= nd then populated it with data:

/Library/Post= greSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT INTO sql_= acsii (data) VALUES ('[Windows-1252]=C2=A0 =C2=A0Euro: \x80=C2=A0 =C2= =A0Double dagger: \x87');"
/Library/PostgreSQL/9.4/bin/p= sql -d sql_ascii -U postgres -c "INSERT INTO sql_ascii (data) VAL= UES ('[Latin-1]=C2=A0 =C2=A0Yen: \xa5=C2=A0 =C2=A0Half: \xbd');&quo= t;
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres= -c "INSERT INTO sql_ascii (data) VALUES ('[Japanese]=C2=A0 =C2=A0= 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]=C2=A0 Blob: \xf4\xa5\xa3\xa5');"
=

I then right-clicked the table in the treeview, and sel= ected the option to view all rows, and immediately saw an error:
=
2018-06-05 12:23:27,319: SQL pgadmin: Exec= ute (async) for server #1 - CONN:1187535 (Query-id: 8522474):
SEL= ECT * FROM public.sql_ascii
ORDER BY id ASC=C2=A0
2018-= 06-05 12:23:27,320: ERROR pgadm= in: Failed to execute query (ex= ecute_async) for the server #1 - CONN:1187535(Query-id: 8522474):
Error Message:ERROR:=C2=A0 invalid byte sequence for encoding "UTF8&q= uot;: 0x80
SQL state: 22021

Runnin= g "SELECT * FROM sql_ascii" in the query tool resulted in the sam= e error, however, if I ran "SET client_encoding =3D '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_encodin= g to SQL_ASCII if it's a SQL_ASCII database?
=C2=A0
It is by default same as the server = encoding. But, the following existing code in=C2=A0=C2=A0w= eb/pgadmin/utils/driver/psycopg2/connection.py makes the client= _encoding as UNICODE for every connection. I am not sure it should be remov= ed.

=C2=A0 =C2=A0 = =C2=A0 =C2=A0 status =3D _execute(cur, "SET DateStyle=3DISO;&qu= ot;

=C2=A0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 "SET client_min_messages=3Dnotice;&quo= t;

=C2=A0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 "SET bytea_output=3Descape;"

=C2=A0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 "SET client_encoding=3D'UNICODE= 9;;")


<= /div>
It was probably before you joined, but I have said a = number of times that pgAdmin 3 handled this differently and that maybe we s= hould do it the same way here. See=C2=A0https://git.postgresql.org/gitweb/?p=3Dpgadmin3.git;a=3Dblob;f=3Dpgadmin/db/pgConn.cpp, in the pgConn::Initialize() function.

Either way, your patch isn't working for me.
=C2=A0


=
Note that this testing was on Python 2.7.10 on MacOS.
= =C2=A0

Kindly review.

Thanks and Regards,
Aditya Toshniwal=
Software Engi= neer |=C2=A0EnterpriseDB Software Solutions |=C2=A0Pune
"Don&= #39;t Complain about Heat, Plant a tree"

On Tue, Jun 5,= 2018 at 10:15 AM, Aditya Toshniwal <aditya.toshniwal@ente= rprisedb.com> wrote:
Hi

On Tue, Jun 5, 2018 at 1:08 AM, Joao De Almeida P= ereira <jdealmeidapereira@pivotal.io> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
Hello Adi= tya,


There is no change= related to notifications in this patch.=C2=A0
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.

-=C2= =A0 =C2=A0 =C2=A0 =C2=A0 # Check for the asynchronous notifies statements.<= /div>
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.check_notifies(True)
= -=C2=A0 =C2=A0 =C2=A0 =C2=A0 notifies =3D conn.get_notifies()
+= =C2=A0 =C2=A0 =C2=A0 =C2=A0 if status is not None:
+=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 # Check for the asynchronous notifies statemen= ts.
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.check_notifie= s(True)
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 notifies =3D c= onn.get_notifies()


This is a minor fix, but is it related to querying SQL_ASCI= I database?
No its not. It is som= ething I found when I was working on SQL_ASCII related changes.
W= ell then, will send a separate patch for it.
<= br>
Thanks
Victoria && Joao





--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pg= snake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL C= ompany



<= div>
--
Dave= Page
Blog: ht= tp://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: = http://www.enterp= risedb.com
The Enterprise PostgreSQL Company




--
Dave Page
Blog: http://pgsnake.blogspot.= com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The = Enterprise PostgreSQL Company




-- <= br>
Dave Page
Blog: http://pgsnake.blogspot.com
Tw= itter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Po= stgreSQL Company




--
Dave Page
Blog: http://pgsnake.blogspo= t.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
Th= e Enterprise PostgreSQL Company




--
= Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB = UK: http://www.en= terprisedb.com
The Enterprise PostgreSQL Company

--000000000000738de4056e93e086--