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 1fQB03-0003ks-1g for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 12:23:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQB01-000231-Vd for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 12:23:41 +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 1fQAxY-0000ko-3n for pgadmin-hackers@lists.postgresql.org; Tue, 05 Jun 2018 12:21:08 +0000 Received: from mail-lf0-x244.google.com ([2a00:1450:4010:c07::244]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQAxT-00048g-N6 for pgadmin-hackers@postgresql.org; Tue, 05 Jun 2018 12:21:06 +0000 Received: by mail-lf0-x244.google.com with SMTP id n3-v6so3203114lfe.12 for ; Tue, 05 Jun 2018 05:21:03 -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=QXdD9/QpqPmsm9S3kOnLV7+FNo1PFOgjoV63M38EvNs=; b=ZGVQElXydmTg88mukS/jWdh4iyLNqkR41j1IjHOBM8PKePT6rhulPXTHMZbw2PUqDy 3nqADUO3uBvKzLOLPfybHI027vBxEma93YAr1K0gHZx/bgV9P0LwYqZBgbNe0gQD3M7J s3+hnEb7SccMA39e0yp/toBwjj4Wpo/q5WGl+N+gcver4pnZjWfuFLyxFipfRUC+DVxh ZXau6pt0C+kzT1a8PssCGlcoibCAFAw+GRCSzfl5/pdQEtVzH4KHmGbpvJtFmkjzZ3b4 DMH+ZCxyJ6hpCpZAqctdUm8uG9775FwjN+hxalDmh2RQiv4nBqj4m9iWaNKqdZa47T5a PNew== 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=QXdD9/QpqPmsm9S3kOnLV7+FNo1PFOgjoV63M38EvNs=; b=OsNixpxY4RqjB8xxuQLBPKZfwZvkYRQBiZdi7R8TqcsAkOZ9PQal3pvrzX0eOWZ/GH 0p3XnaCGA4udSjiv4VXRXrs+BBA9lA06WdUzzuqQfk5Tli6S6VPe69/Xp55kDRCF3x9T JXWJPlbtzL4199uYVPfDvIsYmHxU9OiwsR2pEXxXwZlLwcvHXyLzPyYrUPBHmHD0HLOG 1J2HoZUfwiZH8DJHEblW/ZI+pYY2DRm4iq3tGrG2QN1h7Im9epeJDsBCVl8ctPblbANr xNTkSxTtSHd1wC1ng5v8pr3iem8NItZVg7N67omXd702tsBMnOoa3Am/VV4xTWwDw/p8 NZXQ== X-Gm-Message-State: APt69E1Ja+66XhkG7uPGmuuLnTlYCwWCD3HJvyXFji8rUhd+TDN3w1TH MPYx662SLwVCWn31YhGtudHcxBq8NB7Fx/HVlYjM3A== X-Google-Smtp-Source: ADUXVKIlSgWxlr205GQnPHFXN1duX+DswaOAg41g5ui4r/zVkd4T8/DLueM9KPQsja3XgAE1pazIYwtr3cFDJ28yzEA= X-Received: by 2002:a2e:2ac3:: with SMTP id q186-v6mr4712026ljq.44.1528201260751; Tue, 05 Jun 2018 05:21:00 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a2e:9e8a:0:0:0:0:0 with HTTP; Tue, 5 Jun 2018 05:21:00 -0700 (PDT) In-Reply-To: References: From: Aditya Toshniwal Date: Tue, 5 Jun 2018 17:51:00 +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="0000000000004aeffa056de4150e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000004aeffa056de4150e Content-Type: text/plain; charset="UTF-8" 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 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';") 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 > --0000000000004aeffa056de4150e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Dave,


On Tue, Jun 5, 2018 at 4:56 PM, Dave Page <= dpage@pgadmin.org> wrote:
<= div dir=3D"ltr">Hi

On Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:<= br>
Hi Ha= ckers,

PFA updated patch. The sqleditor change is sent s= eparately and removed from current patch as suggested.
The test c= ases were running fine when the module was specified using --pkg but were f= ailing 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 wit= h data:

/Library/PostgreSQL/9.4/bin/psql= -d sql_ascii -U postgres -c "INSERT INTO sql_acsii (data) VALUES (= 9;[Windows-1252]=C2=A0 =C2=A0Euro: \x80=C2=A0 =C2=A0Double dagger: \x87'= ;);"
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U p= ostgres -c "INSERT INTO sql_ascii (data) VALUES ('[Latin-1]=C2=A0 = =C2=A0Yen: \xa5=C2=A0 =C2=A0Half: \xbd');"
/Library/Post= greSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT INTO sql_= ascii (data) VALUES ('[Japanese]=C2=A0 =C2=A0Ship: \xe8\x88\xb9');&= quot;
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postg= res -c "INSERT INTO sql_ascii (data) VALUES ('[Invalid UTF-8]=C2= =A0 Blob: \xf4\xa5\xa3\xa5');"

I th= en 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 pgadmi= n: Execute (async) for server #= 1 - CONN:1187535 (Query-id: 8522474):
SELECT * FROM public.sql_as= cii
ORDER BY id ASC=C2=A0
2018-06-05 12:23:27,320: ERRO= R pgadmin: Failed to execute query (execute_async) for the ser= ver #1 - CONN:1187535(Query-id: 8522474):
Error Message:ERROR:=C2= =A0 invalid byte sequence for encoding "UTF8": 0x80
SQL= state: 22021

Running "SELECT * FROM sq= l_ascii" in the query tool resulted in the same error, however, if I r= an "SET client_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 mi= ssing? 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 ex= isting code in=C2=A0=C2=A0web/pgadmin/utils/driver/psycopg= 2/connection.py makes the client_encoding as UNICODE for every conne= ction. 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=3Dnoti= ce;"

=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;&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 client_encoding=3D'UNI= CODE';")



Note that this testing was on Python 2.7.10 on MacOS.
<= span class=3D"">
=C2=A0

Kindly review.

Thanks and Regards,
Aditya Toshniwal
Software En= gineer |=C2=A0EnterpriseDB Software Solutions |=C2=A0Pune
"Do= n'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.=C2= =A0
The below code is minor fix related to connection status of s= ql 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 as= ynchronous notifies statements.
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 conn= .check_notifies(True)
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 notifies =3D c= onn.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, but is= it related to querying SQL_ASCII database?
<= /span>
No its not. It is something I found when I was working on SQL_AS= CII related changes.
Well then, will send a separate patch for it= .
<= div class=3D"gmail_quote">

Thanks
Victoria &am= p;& Joao





<= /div>--
Dave Page<= br>Blog: http://p= gsnake.blogspot.com
Twitter: @pgsnake

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

--0000000000004aeffa056de4150e--