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 1fQBca-0006qZ-0O for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 13:03:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQBcY-0006Be-Ph for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 13:03:30 +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 1fQBcY-0006BN-JI for pgadmin-hackers@lists.postgresql.org; Tue, 05 Jun 2018 13:03:30 +0000 Received: from mail-lf0-x229.google.com ([2a00:1450:4010:c07::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQBcQ-0007LW-WE for pgadmin-hackers@postgresql.org; Tue, 05 Jun 2018 13:03:30 +0000 Received: by mail-lf0-x229.google.com with SMTP id y20-v6so3484454lfy.0 for ; Tue, 05 Jun 2018 06:03:22 -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=Zz/i5HM3asewEgPmOA0ZdlUCeo7ofu5TVfVVQC1shZw=; b=BRNLJRRNpqYWqBMgQHxEvhgy6521z5EubMfptMk6VcHNpq/+XONpnevPZNB8eSSp2c 0fGkm9DrS+lwGTUVegg+nBh1isCN9dcdwq8+v3/NuGj5grRsyeDOuwNbS7MhNv3LPZMH KdKPEBRuP4g0ec2Hbi5a2RViM4NkbUnHa09RyEWgez0mtwyMo/TcJO+i0VFlSbcXo1Ve j43+26jzexZ+9+YEwF7Oig7OKsKNamPu8rrutn83iXJj6h+shdmvKTNoOscZFUM9S0N/ MJ0PXgiP/zF4GD3JuMFRTGzEgyVEub2blzkltzLmJdSWm/lLz7d5OJUWgjKRdOd+cr4o hIvA== 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=Zz/i5HM3asewEgPmOA0ZdlUCeo7ofu5TVfVVQC1shZw=; b=fVh/ulcxl/01JLkNhcTIJEDA76Hh+drcUtVdXDRhYovyG2MixQuGygfbkRSdjEffxX IOUQKP1MYN9ZHW3zs6J1Ljkszvn1lu1hOiHJhqLcf9ghpxfV0ftEMMJteeCeeSPvWLb1 wJv+HIGul5QUl3a7UTpxO14MoIl/VgElpZ6uPL42myueHFkk6kdPWRZIHHsW+h0X2mCh rUrbSjrWCN2sJrXuTlfg0MmVvkUWCmDnX4PRtWv/YMv9CF2XnlKQeSQtgF/oo9YpUWFf bURsKbfO4ANkOpYxyHfvfBavY1P51I8LLcQU3IIFPNZGMe2z1sEOI87lFmrYi9tS8WdZ /QZg== X-Gm-Message-State: APt69E00s/sPuhXIihgBTCQfz0o+ewDVn7zMHbJXU0DO58f8epeYesQc ATgVPpRbXqoR64m2Tn4z1rpAVEtKfsyJH5tmO5wBKS9m X-Google-Smtp-Source: ADUXVKILf3IBG/e31vwHVAgcl5pJMFyon+luKiTehX4qKJlm7wv41tMrILpHHCprT15eFfP20ys81qJZNskPdVPSwf4= X-Received: by 2002:a19:5c4b:: with SMTP id q72-v6mr1562245lfb.128.1528203801510; Tue, 05 Jun 2018 06:03:21 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a2e:9e8a:0:0:0:0:0 with HTTP; Tue, 5 Jun 2018 06:03:21 -0700 (PDT) In-Reply-To: References: From: Aditya Toshniwal Date: Tue, 5 Jun 2018 18:33:21 +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="000000000000bbd82d056de4ac8d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000bbd82d056de4ac8d Content-Type: text/plain; charset="UTF-8" Hi On Tue, Jun 5, 2018 at 6:25 PM, Dave Page wrote: > > > On Tue, Jun 5, 2018 at 1:49 PM, 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. > > >> 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 > --000000000000bbd82d056de4ac8d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

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


On Tu= e, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal <aditya.tosh= niwal@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 a= s I said (and showed the example of).
<= div>
After setting the client_encoding to SQL_ASCII you got t= he output. Previously, it used to fail in the back end itself because pytho= n encoding failure. That is fixed.
The error=C2=A0ERROR:=C2=A0 invalid byte sequence for encoding "UTF8&= quot;: 0x80=C2=A0is thrown by postgres and not python or pgAd= min4. You will get the same error even if you=C2=A0
connect from = psql.
=C2=A0
I can see t= here is no SET call in pgAdmin3 for client_encoding.=C2=A0 I can remove the= =C2=A0SET client_encoding=3D'UNICODE'; <= /span>that will solve the problem. But, can you p= lease let me know why that was added.=C2=A0
<= br>
There is, but it's inside an API call (PQsetC= lientEncoding):

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(&quo= t;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=A0= encoding.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=A0wxLogErro= r(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}
3= 05

Oops ! Missed th= at. Apologies.=C2=A0
<= div class=3D"gmail_extra">
=C2=A0

Wi= ll remove the set call and will send you the updated patch if everything wo= rks fine.

No, we n= eed 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
Need to rework on the initialise method. Will come with an updated. patc= h. Sorry for trouble.=C2=A0
=


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

<= div class=3D"m_-8242036136222913340gmail-m_3397243500728641691h5">On Tue, J= un 5, 2018 at 1:21 PM, Aditya Toshniwal <aditya.toshniwal@= enterprisedb.com> wrote:
Hi Dave,


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

On= Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniwal <aditya.tos= hniwal@enterprisedb.com> wrote:
Hi Hackers,

PFA updated patch. The sqleditor change is sent separately and removed fr= om current patch as suggested.
The test cases were running fine w= hen the module was specified using --pkg but were failing in complete run. = Fixed that.

I did a quic= k 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 postgre= s -c "INSERT INTO sql_acsii (data) VALUES ('[Windows-1252]=C2=A0 = =C2=A0Euro: \x80=C2=A0 =C2=A0Double dagger: \x87');"
/Li= brary/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/ps= ql -d sql_ascii -U postgres -c "INSERT INTO sql_ascii (data) VALUES (&= #39;[Japanese]=C2=A0 =C2=A0Ship: \xe8\x88\xb9');"
/Libra= ry/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT IN= TO sql_ascii (data) VALUES ('[Invalid UTF-8]=C2=A0 Blob: \xf4\xa5\xa3\x= a5');"

I then right-clicked the tab= le in the treeview, and selected the option to view all rows, and immediate= ly 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 i= d ASC=C2=A0
2018-06-05 12:23:27,320: ERROR pgadmin: = Failed to execute query (execute_async) for the server #1 - CONN:1187535(Qu= ery-id: 8522474):
Error Message:ERROR:=C2=A0 invalid byte sequenc= e for encoding "UTF8": 0x80
SQL state: 22021

Running "SELECT * FROM sql_ascii" in the que= ry tool resulted in the same error, however, if I ran "SET client_enco= ding =3D 'SQL_ASCII';" first, I do see results.

=
I have confirmed that I've restarted the server after applyi= ng 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?=
=C2=A0
It is by = default same as the server encoding. But, the following existing code in=C2= =A0=C2=A0web/pgadmin/utils/driver/psycopg2/connection= .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 DateStyle=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=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


<= /div>
Note that this test= ing was on Python 2.7.10 on MacOS.
=C2=A0

= Kindly review.

Thanks and Regards,<= /font>
Aditya Toshniwal
Software E= ngineer |=C2=A0EnterpriseDB Software Solutions |=C2=A0Pune
"D= on't Complain about Heat, Plant a tree"
<= /div>

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

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


There is no change related to notification= s in this patch.=C2=A0
The below code is minor fix related to con= nection status of sql editor. Can you please share the code snippet if it i= s 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()


This i= s 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





--
http://pgsnake.blogspot.com
Twitter: @pgsnake

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




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

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




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

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

--000000000000bbd82d056de4ac8d--