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 1fQBT6-00062m-Pg for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 12:53:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQBT4-0006oa-KU for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 12:53:42 +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 1fQBT4-0006oQ-7B for pgadmin-hackers@lists.postgresql.org; Tue, 05 Jun 2018 12:53:42 +0000 Received: from mail-lf0-x235.google.com ([2a00:1450:4010:c07::235]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQBT0-0004pn-AB for pgadmin-hackers@postgresql.org; Tue, 05 Jun 2018 12:53:40 +0000 Received: by mail-lf0-x235.google.com with SMTP id t134-v6so3395807lff.6 for ; Tue, 05 Jun 2018 05:53:38 -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=CCsKUNK3X5ml4bsOK0e2VDXUSNSkYPTaPUEcP5ytXiU=; b=Ok6RCO7wJpzhUf7LPn32/LT1ZFVoNiHONw83i2aVWnNdFKLNzv34a8xgdzJ6zPcAQH ql1hYsc7rvbD3iZjn3g8QiDM5Tfi7t345mZ1w5hqR122KpHDBoL2FxosWpX6uu4KVHYv d8AZb1LWSAqKvgfJtDD2JrmVwPmEmwpHyEckIYa4OXGREQyBHpJ6PVWMK8xS3F5HtfVH /1nrHBAvxtotUTG5tGXYr/wi2DT44RdYR1HccmziX5BtLlwmT5uEuKUxA7XEZscYoFSR yzM3svySTEmXrhfEPgBSUTqdeVQ0erjyHCJrSyAVqzd743HHqKP/6lGMo+yxpLmFTfip dcsQ== 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=CCsKUNK3X5ml4bsOK0e2VDXUSNSkYPTaPUEcP5ytXiU=; b=bHIL4V0JEWPtUCHR3ud8qtvLAOtga5Of+l6yiXQqy31ftJf2zdR7C3tUXOXGozY6H4 IS58/K2rnMG1wycD7jkVOHfi/TFYZUIf1Qm0QxY7u3ynse/4Bcp2K6U2i/oPl5yEA9jS q1ZODXw0q9PV1yYklF8O7sZkJUCfKzvQpQOZyN8QGqckbUE8W2ekRW0YfpWGDeMuAWgY EjZx+mdDV4Vc4gN3N3k3dqB1tOkg/MqxkGmXkh1dGGuh7RvKq3SRF2zoogJukVPTj/nT RDqkVnSrhDUh2vDfk3/IpqBz5HwKKVbFME+nqcNeAOBZgJhyWgH55+buhQIJmqVJWz+F RtuA== X-Gm-Message-State: APt69E3CHnVTmLpbZyQ79DE9O1q+2ObXB5OrezNqIcFxblfHKELNBeuK MCJXUpZi0klZssab0bpr7mICEYqDJzx4m3pXhZf73Q== X-Google-Smtp-Source: ADUXVKI6x6SeyZ75dWqW5Wr4jwp0e+K12r3SkuQ2N6zvoMY6DChd2IGhC52Z9XUAQr7MPE28STj0T2R+zER8Vx0sxuE= X-Received: by 2002:a19:5c4b:: with SMTP id q72-v6mr1539888lfb.128.1528203216351; Tue, 05 Jun 2018 05:53:36 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a2e:9e8a:0:0:0:0:0 with HTTP; Tue, 5 Jun 2018 05:53:35 -0700 (PDT) In-Reply-To: References: From: Aditya Toshniwal Date: Tue, 5 Jun 2018 18:23:35 +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="000000000000db0601056de489e9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000db0601056de489e9 Content-Type: text/plain; charset="UTF-8" Also, there are limitations in psycopg2 as well related to encoding, which makes it even more difficult to make behaviour similar to pgAdmin3 as there are encodings and decodings done at pyscopg2 end also. 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:19 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. 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. > > Will remove the set call and will send you the updated patch if everything > works fine. > > > 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 >> > > --000000000000db0601056de489e9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Also, there are limitations in psycopg2 as well related to= encoding, which makes it even more difficult to make behaviour similar to = pgAdmin3 as there are encodings and decodings done at pyscopg2 end also.

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

On Tue, Jun 5, 2018 at 6:19 PM, Aditya Toshn= iwal <aditya.toshniwal@enterprisedb.com> wro= te:
Hi Dave,

The problem of SQL ASCII is solved with the patch, and not related to se= tting the client encoding of the sql window. 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

Will remove the set call and will send you the updated patc= h if everything works fine.

=

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 6:05 PM, Dave Page <dpage@pgadmin.org> wrote:
Hi

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

=

On Tue, Jun 5, 2018 at 4:56 PM, Dave Page <dpage@pgad= min.org> 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 separa= tely and removed from current patch as suggested.
The test cases = were running fine when the module was specified using --pkg but were failin= g in complete run. Fixed that.

I did a quick test by creating a SQL_ASCII database containing a sim= ple table:

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

And then populated it with dat= a:

/Library/PostgreSQL/9.4/bin/psql -d s= ql_ascii -U postgres -c "INSERT INTO sql_acsii (data) VALUES ('[Wi= ndows-1252]=C2=A0 =C2=A0Euro: \x80=C2=A0 =C2=A0Double dagger: \x87');&q= uot;
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgr= es -c "INSERT INTO sql_ascii (data) VALUES ('[Latin-1]=C2=A0 =C2= =A0Yen: \xa5=C2=A0 =C2=A0Half: \xbd');"
/Library/Postgre= SQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT INTO sql_asc= ii (data) VALUES ('[Japanese]=C2=A0 =C2=A0Ship: \xe8\x88\xb9');&quo= t;
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres= -c "INSERT INTO sql_ascii (data) VALUES ('[Invalid UTF-8]=C2=A0 B= lob: \xf4\xa5\xa3\xa5');"

I then ri= ght-clicked the table in the treeview, and selected the option to view all = rows, and immediately saw an error:

2018-06-0= 5 12:23:27,319: SQL pgadmin: Execute (async) for server #1 - C= ONN:1187535 (Query-id: 8522474):
SELECT * FROM public.sql_ascii
ORDER 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:1187535(Query-id: 8522474):
Error Message:ERROR:=C2=A0 i= nvalid byte sequence for encoding "UTF8": 0x80
SQL stat= e: 22021

Running "SELECT * FROM sql_asc= ii" in the query tool resulted in the same error, however, if I ran &q= uot;SET client_encoding =3D 'SQL_ASCII';" first, I do see resu= lts.

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?
=C2=A0<= /div>
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 ever= y 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;"

=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';&quo= t;)


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 d= o it the same way here. See=C2=A0= https://git.postgresql.org/gitweb/?p=3Dpgadmin3.git;a=3Dblob;f=3D= pgadmin/db/pgConn.cpp, in the pgConn::Initialize() function.
=
Either way, your patch isn't working for me.
<= div class=3D"m_7525130724544157540h5">
=C2=A0
<= div class=3D"gmail_quote">


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

Kindly review.
<= div class=3D"gmail_extra">
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 10:15 AM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:
Hi

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


=
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.
<= br>
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 # Check for the asynchronou= s notifies statements.
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.check_no= tifies(True)
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 notifies =3D conn.get_n= otifies()
+=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 asynchron= ous 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, but is it relate= d to querying SQL_ASCII database?
No its not. It is something I found when I was working on SQL_ASCII relate= d changes.
Well then, will send a separate patch for it.

Thanks
Victoria && J= oao





--
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 Enterpr= ise PostgreSQL Company


--000000000000db0601056de489e9--