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 1fQBVT-0006Gm-6l for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 12:56:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQBVR-0004wa-F2 for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 12:56:09 +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 1fQBVP-0004bD-JU for pgadmin-hackers@lists.postgresql.org; Tue, 05 Jun 2018 12:56:07 +0000 Received: from mail-wm0-x233.google.com ([2a00:1450:400c:c09::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQBVK-0004vc-QT for pgadmin-hackers@postgresql.org; Tue, 05 Jun 2018 12:56:05 +0000 Received: by mail-wm0-x233.google.com with SMTP id r15-v6so4747046wmc.1 for ; Tue, 05 Jun 2018 05:56:02 -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=FyGUskuSLZ00yvWfGtgS9qd/icYjFqZebLHHi8jcdNw=; b=LL6VcIf7yHFMeoUEB6z4l8CBG73STJn9ITyLIJgJ7RSxtWGET1pbo4kDENDDMyTuEi sH/fwe+qxXJwRzlWPcrt3Hton61Vd7GsGJK0CcokgCMzt1F2eZi1S+49uPqCIFAG5N7d ZWFVhxKFxDafhYAa5j+Zkok16zv/WTwVTgiBYY/yJOjen53mBXxz8Nbixb+rag1YAtDy +9Pp1cux3FgCqCFQ7MqrgaLWFRTvE/MRwnB92M95zA270aOedsHRK/xgkLua8PGMx3C8 B0xC/y+6uePJueTRvfJSYdQoyVlvmetxqZH+D6+8cvfGYqVfp4VnbUJ5ShomnWVbRiik 9z1g== 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=FyGUskuSLZ00yvWfGtgS9qd/icYjFqZebLHHi8jcdNw=; b=AcHhkJiGVCrdKBrX6WtQw/qQ56Rcmgou4PLZZYhB5xZQoCSSXOv3x3G2O5a9SYQsPY nicLt8xCy4K+XnNcWP5WnNng8W1b/Xdf+IeqZ/I0YnSzo1d2uIaHfKdWxx62B5vAzrLX l16bTbIdMKKe1dAINMlVrDBlQoNc20jR2a4lNYxMm1qqZ1NOdZKxim025Ly+kBht8PI5 MmK2/Ga0A8BrK2uGrqOxAElg9xxM0dhGHUjqjDj6EACVCBX1ZNFWLk+bR4i+ugReXTZ5 RoBDGuPvz+BNJjbFk8Uw7nTJbQG75U7/pUPN4wOruV9tNByfZrlFE5G+qfL/CWKYMfxV mf6w== X-Gm-Message-State: APt69E2tmzUJ62G+5hb//1x/4P04wme3uplHYtzeva179dUMcgEWn9GL 6d/l/aZ6MN/H4y/EnVOuSM7fkuZmbnGtmPpJXJrF2g== X-Google-Smtp-Source: ADUXVKLVGF7QLcYAdxYoMHuLdJBWF1dhSZugGEJboiVEnq42+Ohm6rqknBkOc8eQ63aJATVDf43yJVuIUs515gG4aBY= X-Received: by 2002:a1c:ec84:: with SMTP id h4-v6mr12562074wmi.154.1528203360736; Tue, 05 Jun 2018 05:56:00 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a1c:2907:0:0:0:0:0 with HTTP; Tue, 5 Jun 2018 05:55:59 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Tue, 5 Jun 2018 13:55:59 +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="000000000000762968056de492d6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000762968056de492d6 Content-Type: text/plain; charset="UTF-8" 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). > 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 > > 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). > > > 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: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 --000000000000762968056de492d6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Tue, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal <= adit= ya.toshniwal@enterprisedb.com> wrote:
Hi Dave,

The p= roblem of SQL ASCII is solved with the patch, and not related to setting th= e client encoding of the sql window.

No it's not. It doesn't work for me as I said (and showed the exam= ple of).
=C2=A0
I can see there is no SET call in pgAdmin3 for clien= t_encoding.=C2=A0 I can remove the=C2=A0SET clie= nt_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 ins= ide 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(conn,=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=A0wxLogError(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

=
=C2=A0

Wi= ll remove the set call and will send you the updated patch if everything wo= rks 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).
=C2=A0
=


Than= ks and Regards,
Adit= ya Toshniwal
Software Engineer |=C2=A0EnterpriseDB Software Solutions |=C2=A0Pune
"Don't Complain about Heat, Plant a tree"

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

On Tue, Jun 5, 2018 at 1:21 PM, Aditya T= oshniwal <aditya.toshniwal@enterprisedb.com> wrote:
Hi Dave,


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

= On Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniw= al <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 cont= aining a simple table:

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

And then populated= it with data:

/Library/PostgreSQL/9.4/bin/ps= ql -d sql_ascii -U postgres -c "INSERT INTO sql_acsii (data) VALU= ES ('[Windows-1252]=C2=A0 =C2=A0Euro: \x80=C2=A0 =C2=A0Double dagger: \= x87');"
/Library/PostgreSQL/9.4/bin/psql -d sql_asc= ii -U postgres -c "INSERT INTO sql_ascii (data) VALUES ('[Latin-1]= =C2=A0 =C2=A0Yen: \xa5=C2=A0 =C2=A0Half: \xbd');"
/Libra= ry/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT IN= TO sql_ascii (data) VALUES ('[Japanese]=C2=A0 =C2=A0Ship: \xe8\x88\xb9&= #39;);"
/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 selected the option to= view all rows, and immediately saw an error:

2018-06-05 12:23:27,319: SQL p= gadmin: Execute (async) for ser= ver #1 - CONN:1187535 (Query-id: 8522474):
SELECT * FROM public.s= ql_ascii
ORDER BY id ASC=C2=A0
2018-06-05 12:23:27,320:= ERROR pgadmin: Failed to execute query (execute_async) for th= e server #1 - CONN:1187535(Query-id: 8522474):
Error Message:ERRO= R:=C2=A0 invalid byte sequence for encoding "UTF8": 0x80
SQL state: 22021

Running "SELECT * FR= OM sql_ascii" in the query tool resulted in the same error, however, i= f I ran "SET client_encoding =3D 'SQL_ASCII';" first, I d= o see results.

I have confirmed that I've rest= arted the server after applying the patch.

What am= I missing? Why don't we just set the client_encoding to SQL_ASCII if i= t'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=A0web/pgadmin/utils/dr= iver/psycopg2/connection.py makes the client_encoding as UNICOD= E for every connection. I am not sure it should be removed.

<= /div>

=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


Note that this testing 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, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:
Hi

On Tue, Jun 5, 2= 018 at 1:08 AM, Joao De Almeida Pereira <jdealmeidapereira@pivo= tal.io> wrote:
Hello Aditya,
<= 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">

=
There is no change related to notifications in this patch.= =C2=A0
The below code is minor fix related to connection status o= f 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.
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 c= onn.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()

=

This is a minor fix, bu= t is it related to querying SQL_ASCII database?
No its not. It is something I found when I was working on SQ= L_ASCII related changes.
Well then, will send a separate patch fo= r it.

Thanks
Victoria= && Joao





--
Dave Page
Blog: <= a href=3D"http://pgsnake.blogspot.com" target=3D"_blank">http://pgsnake.blo= gspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.comThe Enterprise PostgreSQL Company




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

EnterpriseDB UK: http://www.enterprisedb.comThe Enterprise PostgreSQL Company




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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Compan= y
--000000000000762968056de492d6--