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 1fQBmD-0007cR-Br for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 13:13:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQBmC-0007MZ-Em for pgadmin-hackers@arkaria.postgresql.org; Tue, 05 Jun 2018 13:13:28 +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 1fQBlV-0005Oj-Pq for pgadmin-hackers@lists.postgresql.org; Tue, 05 Jun 2018 13:12:45 +0000 Received: from mail-wr0-x22e.google.com ([2a00:1450:400c:c0c::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQBlQ-0007cl-HD for pgadmin-hackers@postgresql.org; Tue, 05 Jun 2018 13:12:44 +0000 Received: by mail-wr0-x22e.google.com with SMTP id 94-v6so2383691wrf.5 for ; Tue, 05 Jun 2018 06:12:40 -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=7wUgD8mgY9CbRx8pqb+9lgy6V0Ft/Evdl0x9fIqT6yg=; b=LD7oafJAnaONL2cuE1QsC/jHXz+60OxqrkYR1NoPmptMN8u7O2kYkcr3hxA6A73JBV 7RKCnq0RGv2lKTnBqcGQW0aDmIgB2QPtETRck3k0i5pXELiq7S865qJftvv9fTGdboks r9jSBDWnluPJrap9lMzs26ftGKR7JWMllLEV0L7OIeQcpsNoarrl+8GvovWIEOwdK92w q9lFUHwfT8Lh/UFUfYD61KHPPVqoFd2JGEIk95bsug8eHJY9LRYN0WkT2xXCLbjvfWZY nZK2g9LkSY+ySTVApq/G+kFNVmTUnpy+4e6qFOZ2cQnRmAarEPOK7KDrs92uUQgaOZiY a8Zg== 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=7wUgD8mgY9CbRx8pqb+9lgy6V0Ft/Evdl0x9fIqT6yg=; b=DWg3+krBT437nXZs/H+kHiG/4zPGHigskIunyjaTI1IdRtpAhZrHl/u6W+T8+wUfSn UdsR/CgtJApnETxFNcIARcz4Yradouo4tkyUeFqACpnHXTjQEU25dMwiCvNT2k3Vr4Cm tsGGQTVO2ltgzFKBtwnzBMELScAQP0LyDDb5tUyZqBNS6+pmbFglxNnkfcwch7AtKcqj QKqH64feXWpD89APwjeUruPY2rnk/hkawmC45zZMg8sncEEcvue7ZFNbOy5aSSjRkcRZ 1ktkiQIMt8zb4rjg43/1FHt0zGvIu0Cac2EZ2CNlpgUkEsQGI9M4jUHdH/3GMKn3wGRI 0s+g== X-Gm-Message-State: ALKqPwfMGCW81lY8FUyMxSLU5b8a7kRyNSfKBYOMxY5arf+lViOs2nqS h5enHWOLV3SVFgYy1j0cWirYEtPcqI3jT5Ef79DTZw== X-Google-Smtp-Source: ADUXVKJzWlZhYQoM7L4qBx07FNObdB3jvNL33w5tCtFgrllNYPd0xeL3gYsf77QDqWigbm1erxUFFaoq462i0QImxH8= X-Received: by 2002:adf:f090:: with SMTP id n16-v6mr20443415wro.49.1528204359227; Tue, 05 Jun 2018 06:12:39 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a1c:2907:0:0:0:0:0 with HTTP; Tue, 5 Jun 2018 06:12:37 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Tue, 5 Jun 2018 14:12:37 +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="000000000000f9eb03056de4cd24" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000f9eb03056de4cd24 Content-Type: text/plain; charset="UTF-8" Hi On Tue, Jun 5, 2018 at 2:03 PM, Aditya Toshniwal < aditya.toshniwal@enterprisedb.com> wrote: > Hi > > On Tue, Jun 5, 2018 at 6:25 PM, Dave Page wrote: > >> >> >> 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). >> > > 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. > Sure - but that is not a fix. You have no way of running the SET command if you're using "view data" - and in the query tool, users just expect it to work (as it did in pgAdmin 3). > >> >>> 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 >> > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company --000000000000f9eb03056de4cd24 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

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

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


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).

After setting the client_encoding to SQL= _ASCII you got the output. Previously, it used to fail in the back end itse= lf because python encoding failure. That is fixed.
The error=C2= =A0ERROR:=C2=A0 invalid byte sequence for en= coding "UTF8": 0x80=C2=A0is thrown by postgres and = not python or pgAdmin4. You will get the same error even if you=C2=A0
=
connect from psql.

=
Sure - but that is not a fix. You have no way of running the SET comma= nd if you're using "view data" - and in the query tool, users= just expect it to work (as it did in pgAdmin 3).
=C2=A0
<= div>=C2=A0
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 pro= blem. But, can you please let me know why that was added.=C2=A0

There is, but it's inside an A= PI 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=A0wxLogI= nfo(wxT("Setting=C2=A0client_encoding=C2=A0to=C2=A0'%s'&q= uot;),=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(&quo= t;%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

<= /div>
Oops ! Missed that. Apologies.=C2=A0
=C2=A0
=

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

No, we need to ensure the clien= t 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= . patch. Sorry for trouble.=C2=A0


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

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


On T= ue, Jun 5, 2018 at 4:56 PM, Dave Page <dpage@pgadmin.org> wr= ote:
Hi

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

PFA updated patch. The sqle= ditor 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_ASCI= I database containing a simple table:

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

And= then populated it with data:

/Library/Postgr= eSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT INTO sql_ac= sii (data) VALUES ('[Windows-1252]=C2=A0 =C2=A0Euro: \x80=C2=A0 =C2=A0D= ouble dagger: \x87');"
/Library/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/psql -d sql_ascii -U postgres -c = "INSERT INTO sql_ascii (data) VALUES ('[Japanese]=C2=A0 =C2=A0Ship= : \xe8\x88\xb9');"
/Library/PostgreSQL/9.4/bin/psql= -d sql_ascii -U postgres -c "INSERT INTO sql_ascii (data) VALUES (= 9;[Invalid UTF-8]=C2=A0 Blob: \xf4\xa5\xa3\xa5');"

I then right-clicked the table in the treeview, and selecte= d 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=C2=A0
2018-06-0= 5 12:23:27,320: ERROR pgadmin:<= span style=3D"white-space:pre-wrap"> Failed to execute query (execut= e_async) for the server #1 - CONN:1187535(Query-id: 8522474):
Err= or Message:ERROR:=C2=A0 invalid byte sequence for encoding "UTF8"= : 0x80
SQL state: 22021

Running &q= uot;SELECT * FROM sql_ascii" in the query tool resulted in the same er= ror, however, if I ran "SET client_encoding =3D 'SQL_ASCII';&q= uot; first, I do see results.

I have confirmed tha= t 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?
=

=
Either way, your patch isn't working for me.
<= div class=3D"m_-2889938182068220083m_-8242036136222913340gmail-m_3397243500= 728641691h5">
=C2=A0


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

Kindly review.

Thanks and Regards,<= div>Aditya Toshniwal
Software Enginee= r |=C2=A0EnterpriseDB Software Solutions |=C2=A0Pune
"Don&#= 39;t Complain about Heat, Plant a tree"

On Tue, Jun 5, 2= 018 at 10:15 AM, Aditya Toshniwal <aditya.toshniwal@enterp= risedb.com> wrote:
Hi

On Tue, Jun 5, 2018 at 1:08 AM, Joao De Almeida Per= eira <jdealmeidapereira@pivotal.io> wrote:
Hello Adity= a,


There is no change r= elated to notifications in this patch.=C2=A0
The below code is mi= nor fix related to connection status of sql editor. Can you please share th= e code snippet if it is not the below.

-=C2= =A0 =C2=A0 =C2=A0 =C2=A0 # Check for the asynchronous notifies statements.<= /div>
-=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 statemen= ts.
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.check_notifie= s(True)
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 notifies =3D c= onn.get_notifies()


This is a minor fix, but is it related to querying SQL_ASCI= I database?
No its not. It is som= ething I found when I was working on SQL_ASCII related changes.
W= ell then, will send a separate patch for it.
<= br>
Thanks
Victoria && Joao





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

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




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

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





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

EnterpriseDB UK: http://www.enterprisedb.com<= br>The Enterprise PostgreSQL Company
--000000000000f9eb03056de4cd24--