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 1fQspG-0001Du-Da for pgadmin-hackers@arkaria.postgresql.org; Thu, 07 Jun 2018 11:11:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQspF-0005ld-6m for pgadmin-hackers@arkaria.postgresql.org; Thu, 07 Jun 2018 11:11:29 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fQspE-0005lT-K6 for pgadmin-hackers@lists.postgresql.org; Thu, 07 Jun 2018 11:11:29 +0000 Received: from mail-wr0-x22d.google.com ([2a00:1450:400c:c0c::22d]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQsp5-0000AO-Pe for pgadmin-hackers@postgresql.org; Thu, 07 Jun 2018 11:11:26 +0000 Received: by mail-wr0-x22d.google.com with SMTP id l41-v6so9641592wre.7 for ; Thu, 07 Jun 2018 04:11:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin-org.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=08mtlpSumxgFM46YnmMzbMcsnEiUmWqoaj6/+gAjisY=; b=XCmgwHOv14+PbfLbrO/v8LuPHiyC4hctEV9B76RG1gQh0j5OsXptXRTUr0YRwBFKka /zLPtNdrSLhAi0jN9HCmg8dR0napFMD6wfsqbb6GOjrDpmhEez4dlApKirJpOhWsgOo3 z+9sg/EyTq75cODVPvycU15SpBeoZhqbK3hdbsHwvvOVcIm0ok7N98AjsYLYHTFngoFL fRDEmRmGGd40RDKjcArswrz6nMHlO+/CQBdCOGzz6DAWv0mWzSGI5mDLhaS0Vsjd6dEb ALVEZN5lV/0jxPtu7B/tpRhsQGObY6Eb+FrJA+lNUcrY8pGDHGdNRXQ1MZt9trkBQ2xn oj1A== 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=08mtlpSumxgFM46YnmMzbMcsnEiUmWqoaj6/+gAjisY=; b=ctY8opJn8LRTfGxYj61t+qKqs8kKX2/I1psVxUpRZgTw6HKKwmU87USnzeC7DRv14A ZHhZAp5Np1NoZqGn3sxcndb+0RfLUU00AX6TZ2nV3+O2yF5OY3Sjw+FbUZwM3fqnhl5D au45QRdoop++Gpx759Y29TFoiX2R4txrp2wydlb7Y1w8DOZr8Qp8GEFfgLNOe7E1Lubr C8q+Vn8Hx8zMzXew9Sa2TfJBlWZup5Lpo/F9bFgMfH4JDJwVFncMWM2N8wnqOQaicVOZ i0MvDXOgxW05O6+MGkUv+B0+YXdj02PXm3hk4pWbC+58vrzJ7uLK4Isstzz4Zb57aoTm BqLQ== X-Gm-Message-State: APt69E0WDed5Jj1DruEW5Yd9HWl+4lAGxA2BRSDImkTIa6alU+UpldrD q0KRUeJuHBtia1cEmGh4gq2ID51F+bvIxR5KGvNuXQ== X-Google-Smtp-Source: ADUXVKIyYSZK2crSDvsWHTfo9cTrSsw6+TMPunhigTtr46jDzRC6BCCzcIfOvl4Rn6QilxJWn480n1+B5NskDTQIcBU= X-Received: by 2002:adf:9950:: with SMTP id x74-v6mr1265079wrb.135.1528369878055; Thu, 07 Jun 2018 04:11:18 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a1c:2907:0:0:0:0:0 with HTTP; Thu, 7 Jun 2018 04:11:16 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Thu, 7 Jun 2018 12:11:16 +0100 Message-ID: Subject: Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database. To: Aditya Toshniwal Cc: Joao De Almeida Pereira , pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000aace28056e0b57d8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000aace28056e0b57d8 Content-Type: text/plain; charset="UTF-8" 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 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) >> >> >> >>> 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 --000000000000aace28056e0b57d8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

On Thu, Jun 7, 2018 at 12:05 PM, Aditya Toshniwal &= lt;a= ditya.toshniwal@enterprisedb.com> wrote:
Hi Dave,


On Thu, Jun 7, 2018 a= t 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,<= div>
PFA updated patch as the previous one was not work= ing as expected. I have tried to make it similar to that of pgAdmin3 and yo= u do not need to change client_encoding as it is set now based on server en= coding. It works fine with "view data" also.

- In connection.py, at ~409, shouldn't w= e set the client_encoding to SQL_ASCII? Otherwise it could be overridden wi= th something unexpected if the client has PGCLIENTENCODING set for example.=
Yeah I agree, it would be = better to add. Will add the change.=C2=A0

- With or with= out that change, I get the following test failure on macOS with Python 2.7.= 10:
It works fine on my mac= hine with Python 2.7 and macOS. Could you please let me know the Postgres D= B version also.

Pos= tgreSQL 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.0= 0), 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_encod= ing_charset.TestEncodingCharset)
With Encoding SQL_ASCII
----------------------------------------------------------------------
Traceback (most recent call last):
=C2=A0 File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sq= leditor/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 retur= n self.open(*args, **kw)
=C2=A0 File "/Users/dpage/.virtuale= nvs/pgadmin4/lib/python2.7/site-packages/flask/testing.py", = line 127, in open
=C2=A0 =C2=A0 follow_redirects=3Dfollow_redire<= wbr>cts)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin= 4/lib/python2.7/site-packages/werkzeug/test.py", line 803, in ope= n
=C2=A0 =C2=A0 response =3D self.run_wsgi_app(environ, buffered= =3Dbuffered)
=C2=A0 File "/Users/dpage/.virtualenvs/pga= dmin4/lib/python2.7/site-packages/werkzeug/test.py", line 716, in= run_wsgi_app
=C2=A0 =C2=A0 rv =3D run_wsgi_app(self.application,= environ, buffered=3Dbuffered)
=C2=A0 File "/Users/dpage/.vi= rtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py&q= uot;, line 923, in run_wsgi_app
=C2=A0 =C2=A0 app_rv =3D app(envi= ron, start_response)
=C2=A0 File "/Users/dpage/.virtualenvs/= pgadmin4/lib/python2.7/site-packages/flask/app.py", line 199= 7, in __call__
=C2=A0 =C2=A0 return self.wsgi_app(environ, start_= response)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmi= n4/lib/python2.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/si= te-packages/flask/app.py", line 1540, in handle_exception
=C2=A0 =C2=A0 reraise(exc_type, exc_value, tb)
=C2=A0 File &qu= ot;/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages= /flask/app.py", line 1982, in wsgi_app
=C2=A0 =C2=A0 respons= e =3D self.full_dispatch_request()
=C2=A0 File "/Users/dpage= /.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py&q= uot;, line 1614, in full_dispatch_request
=C2=A0 =C2=A0 rv =3D se= lf.handle_user_exception(e)
=C2=A0 File "/Users/dpage/.virtu= alenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", l= ine 1517, in handle_user_exception
=C2=A0 =C2=A0 reraise(exc_type= , exc_value, tb)
=C2=A0 File "/Users/dpage/.virtualenvs/pga<= wbr>dmin4/lib/python2.7/site-packages/flask/app.py", line 1612, i= n full_dispatch_request
=C2=A0 =C2=A0 rv =3D self.dispatch_reques= t()
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib= /python2.7/site-packages/flask/app.py", line 1598, in dispatch_re= quest
=C2=A0 =C2=A0 return self.view_functions[rule.endpoint= ](**req.view_args)
=C2=A0 File "/Users/dpage/.virtualenvs/pg= admin4/lib/python2.7/site-packages/flask_login.py", line 792= , in decorated_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': oids
=C2=A0 File "/Users/dpage/git/pgadmin4= /web/pgadmin/utils/ajax.py", line 61, in make_json_response
=
=C2=A0 =C2=A0 separators=3D(',', ':')),
=C2= =A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-p= ackages/simplejson/__init__.py", line 399, in dumps
=C2= =A0 =C2=A0 **kw).encode(obj)
=C2=A0 File "/Users/dpage/.virt= ualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/encoder.p= y", line 291, in encode
=C2=A0 =C2=A0 chunks =3D self.iteren= code(o, _one_shot=3DTrue)
=C2=A0 File "/Users/dpage/.virtual= envs/pgadmin4/lib/python2.7/site-packages/simplejson/encoder.py&q= uot;, line 373, in iterencode
=C2=A0 =C2=A0 return _iterencode(o,= 0)
UnicodeDecodeError: 'utf8' codec can't decode byt= e 0xad in position 0: invalid start byte

---------= -------------------------------------------------------------
Ran 317 tests in 30.692s

FAILED (errors=3D1= , skipped=3D21)

=C2=A0
The only prob= lem is, I cannot find equivalent codec for wxConvLibc in python. The closes= t 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 r= esults.

Yeah, I think th= at's fine. For the small number of people with SQL_ASCII databases, see= ing 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 be= considered as a plain string.
I= NSERT 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 Enginee= r |=C2=A0EnterpriseDB Software Solutions |=C2=A0Pune
"Don&#= 39;t Complain about Heat, Plant a tree"

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

On Tue, Jun 5, 2018 at 2:03 PM, Adi= tya Toshniwal <aditya.toshniwal@enterprisedb.com&= gt; wrote:
Hi

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


<= span>On Tue, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal <= ;adi= tya.toshniwal@enterprisedb.com> wrote:
Hi Dave,
The problem of SQL ASCII is solved with the patch, and not re= lated 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 g= ot the output. Previously, it used to fail in the back end itself because p= ython encoding failure. That is fixed.
The error=C2=A0ERROR:=C2=A0 invalid byte sequence for encoding "U= TF8": 0x80=C2=A0is thrown by postgres and not python or = pgAdmin4. You will get the same error even if you=C2=A0
connect f= rom psql.

Su= re - but that is not a fix. You have no way of running the SET command if y= ou're using "view data" - and in the query tool, users just e= xpect it to work (as it did in pgAdmin 3).
=C2=A0
<= div dir=3D"ltr">
=C2=A0
I can see there is no SET call in pgAdmin3 for client_encoding.= =C2=A0 I can remove the=C2=A0SET client_encoding= =3D'UNICODE'; that will solve the = problem. But, can you please let me know why that was added.=C2=A0

There is, but it's inside a= n 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(conn,=C2=A0encoding.ToAscii()))
<= div class=3D"gmail-m_-5098864319500379874m_1422883046157713714gmail-m_-5498= 328813472628049gmail-m_584540487799665668gmail-m_-7949188697271137504m_-288= 9938182068220083m_-8242036136222913340gmail-pre" style=3D"font-family:monos= pace;font-size:small;white-space:pre-wrap;color:rgb(0,0,0);font-style:norma= l;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;le= tter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;wo= rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:init= ial;text-decoration-color:initial"> 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=A0wxLogError(wxT(&quo= t;%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}
3= 05

Oops ! Mi= ssed that. Apologies.=C2=A0
=C2=A0
<= div>
Will remove the set call and will send you the updated p= atch 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 beli= eve).
=C2=A0
Need to rework on the initialise method. = Will come with an updated. patch. Sorry for trouble.=C2=A0

<= span class=3D"gmail-m_-5098864319500379874m_1422883046157713714gmail-m_-549= 8328813472628049gmail-m_584540487799665668gmail-m_-7949188697271137504m_-28= 89938182068220083m_-8242036136222913340gmail-">
On Tue, J= un 5, 2018 at 6:05 PM, Dave Page <dpage@pgadmin.org> wrote:<= br>
Hi

On Tue, Jun 5, = 2018 at 1:21 PM, Aditya Toshniwal <aditya.toshniwal@enterp= risedb.com> wrote:
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 AM, Aditya Toshniwal <= adit= ya.toshniwal@enterprisedb.com> wrote:
Hi Hackers,

PFA updated patch. The sqleditor change is sent separately and remo= ved 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 p= ostgres -c "INSERT INTO sql_acsii (data) VALUES ('[Windows-1252]= =C2=A0 =C2=A0Euro: \x80=C2=A0 =C2=A0Double dagger: \x87');"
<= div>/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "= ;INSERT INTO sql_ascii (data) VALUES ('[Latin-1]=C2=A0 =C2=A0Yen: \xa5= =C2=A0 =C2=A0Half: \xbd');"
/Library/PostgreSQL/9.4/bin/= psql -d sql_ascii -U postgres -c "INSERT INTO sql_ascii (data) VA= LUES ('[Japanese]=C2=A0 =C2=A0Ship: \xe8\x88\xb9');"
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "IN= SERT INTO sql_ascii (data) VALUES ('[Invalid UTF-8]=C2=A0 Blob: \xf4\xa= 5\xa3\xa5');"

I then right-clicked = the table in the treeview, and selected the option to view all rows, and im= mediately saw an error:

2018-06-05 12:23:27,3= 19: SQL pgadmin: Execute (async) for server #1 - CONN:1187535 = (Query-id: 8522474):
SELECT * FROM public.sql_ascii
ORD= ER BY id ASC=C2=A0
2018-06-05 12:23:27,320: ERROR pgadmin: = Failed to execute query (execute_async) for the server #1 - CONN:118= 7535(Query-id: 8522474):
Error Message:ERROR:=C2=A0 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 clie= nt_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= 9;t we just set the client_encoding to SQL_ASCII if it's a SQL_ASCII da= tabase?
=C2=A0
It= is by default same as the server encoding. But, the following existing cod= e in=C2=A0=C2=A0web/pgadmin/utils/driver/psycopg2/con= nection.py makes the client_encoding as UNICODE for every connection= . I am not sure it should be removed.

=C2= =A0 =C2=A0 =C2=A0 =C2=A0 status =3D _execute(cur, "SET DateStyl= e=3DISO;"

=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=3Dn= otice;"

=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;&= quot;

=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';")

=

It was probably before you joined, but I ha= ve said a number of times that pgAdmin 3 handled this differently and that = maybe we should 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() fun= ction.

Either way, your patch isn't working fo= r me.
=C2=A0
<= div>

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

Kindly review.

=
Thanks and = Regards,
Aditya Tosh= niwal
S= oftware Engineer |=C2=A0EnterpriseDB Software Solutions |=C2=A0Pune
"Don't Complain about Heat, Plant a tree"

On Tue, Jun 5, 2018 at 10:15 AM, Ad= itya Toshniwal <aditya.toshniwal@enterprisedb.com= > wrote:
Hi

<= span>On Tue, Jun 5, 2018 at 1:08 AM, Joao De Almeida Pereira <j= dealmeidapereira@pivotal.io> wrote:
Hello Aditya,


There is no change related to notificat= ions 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 i= t is not the below.

-=C2=A0 =C2=A0 =C2=A0 =C2= =A0 # Check for the asynchronous notifies statements.
-=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 statements.
+=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.check_notifies(True)
+= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 notifies =3D conn.get_notifies()<= /div>


Thi= s 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.

Thank= s
Victoria && Joao





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

Enterpri= seDB UK: http://w= ww.enterprisedb.com
The Enterprise PostgreSQL Company




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

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




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

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




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

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




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

Ent= erpriseDB UK: htt= p://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 Compan= y
--000000000000aace28056e0b57d8--