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 1fQBEg-0004x2-Nx for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 12:38:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQBEf-0002rY-RG for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 12:38:49 +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 1fQBBU-0000rN-Jx for pgadmin-hackers@lists.postgresql.org; Tue, 05 Jun 2018 12:35:32 +0000 Received: from mail-wr0-x230.google.com ([2a00:1450:400c:c0c::230]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQBBP-0006ib-8f for pgadmin-hackers@postgresql.org; Tue, 05 Jun 2018 12:35:31 +0000 Received: by mail-wr0-x230.google.com with SMTP id d2-v6so2230915wrm.10 for ; Tue, 05 Jun 2018 05:35:26 -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=6mCoKVe2Rfzi3kz5C2vgXhhwvfIkfHzI3x4iNiL3Ax4=; b=Jy6q+f2T0oV/ANeejIleK45Sjo3wMLEbNpXIFdIWObNFQmETARJQTIyqdLiX4mUmyk zdvDLFq0Z2qnljq/tJZyAygHtz/CFbs8ey27+WcO/sGexsJftCnSVJ3Nlp7NlvmQ+Mw8 2ClYWNX1PQwWDfPMNDpJSQv1TGJG3jJ2rcweUJSB6fyYlw/NAv4HL0qOh4P/GY52fiem MCxx/xIpCWy2opXAx72jk73IjYVvGiTu2G9rnUpugOragoih+cvgF1QZWQzPydRUsyHg hdxObLE3dujjxJkXX2XlbTR3Fj0bFZQcWFjAg/U+s5qG5O8/EHfiXF/+Ij80A9MP+3T/ N+5A== 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=6mCoKVe2Rfzi3kz5C2vgXhhwvfIkfHzI3x4iNiL3Ax4=; b=MkVLoBoWtMu0b9sS/X37aVbJ/edo9aOnIaRKW7LNefbmwlwTh2A7+17Vj8easx1hPD C+FcTVz4kt8hm5Hp3W8WWf+8YKSLwZnk+9nobGQ8ATEdEihpfBXcq7GAVyyP0n29kFrO D9VlcCVbxZHE9PBgqmx/yy+Ei3FXwkC/A/lXrHVgdMbK4ARx+z9piWI5t9st1KYTMQRe O5wFqlZvACeW2YsDTpNuABAl5Zgv13F2plXj8uMne+rN3Ziaq/V161G1IwJXIUjNfrSk b3gbpheXUe4govI530TJ4QGblB5I+oulk27k/CT6+DA+Ax9UCFwHCuxgXs9owTFfXsU+ KNVw== X-Gm-Message-State: ALKqPwczYigDEc9W52oH6dryOt/RpA+v1oMB6hf2tJLTwChdh/Z4wXme qMpxiVrJ0f0csRZK7NAMw7UcUMEkUcIAdROUx2dybA== X-Google-Smtp-Source: ADUXVKLbyr4Bv/IlmPCYoThtR89UxemqQLEUsewPoNZyoYd/Nk+QluWW98lTR9q8kNZDn1kJ4yfKNzcCjcO3N4gb1pM= X-Received: by 2002:adf:8211:: with SMTP id 17-v6mr19067636wrb.144.1528202125227; Tue, 05 Jun 2018 05:35:25 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a1c:2907:0:0:0:0:0 with HTTP; Tue, 5 Jun 2018 05:35:24 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Tue, 5 Jun 2018 13:35:24 +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="000000000000d1cdcd056de44862" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000d1cdcd056de44862 Content-Type: text/plain; charset="UTF-8" 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 --000000000000d1cdcd056de44862 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

On Tue, Jun 5, 2018 at 1:21 PM, Aditya Toshniwal &l= t;ad= itya.toshniwal@enterprisedb.com> wrote:
Hi Dave,


On Tue, Jun 5, 2018 a= t 4:56 PM, Dave Page <dpage@pgadmin.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 separately and = removed from current patch as suggested.
The test cases were runn= ing fine when the module was specified using --pkg but were failing in comp= lete 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, d= ata text);

And then populated it with data:
<= div>
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii = -U postgres -c "INSERT INTO sql_acsii (data) VALUES ('[Windows-125= 2]=C2=A0 =C2=A0Euro: \x80=C2=A0 =C2=A0Double dagger: \x87');"
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c &qu= ot;INSERT INTO sql_ascii (data) VALUES ('[Latin-1]=C2=A0 =C2=A0Yen: \xa= 5=C2=A0 =C2=A0Half: \xbd');"
/Library/PostgreSQL/9.4/bin= /psql -d sql_ascii -U postgres -c "INSERT INTO sql_ascii (data) V= ALUES ('[Japanese]=C2=A0 =C2=A0Ship: \xe8\x88\xb9');"
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "I= NSERT INTO sql_ascii (data) VALUES ('[Invalid UTF-8]=C2=A0 Blob: \xf4\x= a5\xa3\xa5');"

I then right-clicked= the table in the treeview, and selected the option to view all rows, and i= mmediately 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
OR= DER 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:11= 87535(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 cli= ent_encoding =3D 'SQL_ASCII';" first, I do see results.
<= div>
I have confirmed that I've restarted the server afte= r applying the patch.

What am I missing? Why don&#= 39;t we just set the client_encoding to SQL_ASCII if it's a SQL_ASCII d= atabase?
=C2=A0
I= t is by default same as the server encoding. But, the following existing co= de in=C2=A0=C2=A0web/pgadmin/utils/driver/psycopg2/co= nnection.py makes the client_encoding as UNICODE for every connectio= n. I am not sure it should be removed.

=C2=A0 =C2=A0 =C2=A0 =C2=A0 status =3D _execute(cur, "SET Da= teStyle=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=3Desca= pe;"

=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&= #39;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=C2=A0https://g= it.postgresql.org/gitweb/?p=3Dpgadmin3.git;a=3Dblob;f=3Dpgadmin/db/pgConn.c= pp, in the pgConn::Initialize() function.

Eith= er 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.

<= div>
Thank= s and Regards,
Adity= a Toshniwal
Software 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:
<= div dir=3D"ltr">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 notifi= cations 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 i= f 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 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()=


Th= is is a minor fix, but is it related to querying SQL_ASCII database?
<= /div>
No its not. It is something I found whe= n I was working on SQL_ASCII related changes.
Well then, will sen= d a separate patch for it.

Than= ks
Victoria && Joao





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

Enterp= riseDB 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 Compan= y
--000000000000d1cdcd056de44862--