public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Page <[email protected]>
To: Aditya Toshniwal <[email protected]>
Cc: Joao De Almeida Pereira <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database.
Date: Thu, 7 Jun 2018 11:37:28 +0100
Message-ID: <CA+OCxoz7fFQn5ADzLbohXknMNk-1kfeTjAgCO4NrsXZPRiFVNw@mail.gmail.com> (raw)
In-Reply-To: <CAM9w-_kPsWEodXtznzA0ZwXSO3d8t2agg5sUzF=2EEsGfakuMw@mail.gmail.com>
References: <CAM9w-_ntCUguZp2rVJXPsDvWJbWneAz4-AHJoYB+nUTSYMXc9g@mail.gmail.com>
<CA+OCxozRjJY6RWrXpMLD6-dWnm2LdPs6bS+VmJa403vxVoLwhg@mail.gmail.com>
<CAM9w-_m-cJax16zqBonx-d0-HSy5dNoOzB13HWAad5Xv0Wu=CA@mail.gmail.com>
<CANxYE3+qrGeZYzdYEtzxS8i2P+325V5E+n1xVpyzTwn7vRqRDg@mail.gmail.com>
<CAM9w-_mTe0gW+2cdhMutMWyBsRt7Om3G_Em126Ryntf0nOiHbA@mail.gmail.com>
<CAM9w-_mXdJhuRFn87yAuqWtfrhnRgw1KvU2PpthL-hbnOt2QpA@mail.gmail.com>
<CAE+jjampAPoDq7xJgeNf1L=i_1jf83PLr=dZYicfkEMp6_UBAg@mail.gmail.com>
<CAM9w-_k2Fo-qDy+qxM5i=Aba__TAWxrryR+m3izTXB=koToKcQ@mail.gmail.com>
<CA+OCxoz+Wo3pUuu8SMBzAsQvrOjYLPQHCfQMtO3X3HKezKcapw@mail.gmail.com>
<CAM9w-_nEDycbeEEeUhKwXjxmkNC39YzqhggJR3Frc4pq6WcJ0Q@mail.gmail.com>
<CAM9w-_kDQkAWRZrRRtmMDz8DZ5x2wGoUro6oQbQqTzLmZZGyBw@mail.gmail.com>
<CAE+jjametYGjStNFZFW544Jcm_by1OABtb7yFQWKGzyfk08QiA@mail.gmail.com>
<CAM9w-_=rsazJWCu5xRqLGkYX3FW22vVuyc0NcR5TPskO9PApUg@mail.gmail.com>
<CAE+jjakwbfsbwkvwa6N1QGjGeVb36sWnde+C6amnHQLHdTE3kQ@mail.gmail.com>
<CAM9w-_km5Zv=Gw6CUZF8m3zzacOKL-ir-QNw3xPjP2MJfLRg7w@mail.gmail.com>
<CAM9w-_=c=82+5ESAEMXUQf5NOrfouZ5JxJMfr+56vJAfGz2DrA@mail.gmail.com>
<CA+OCxoxAhWEF5tQQy84QoOYB=ed--o--ppBoXZ1dwvWNBoNMQw@mail.gmail.com>
<CAM9w-_nUfackUWYNZDRcJ43D6ftYWnWkRsxvcyZVDzABkqza8A@mail.gmail.com>
<CA+OCxoyRgrQ=83Wo1YwsR9oSHE87=-20uP7d7zmF9F685CW2sA@mail.gmail.com>
<CAM9w-_ngHm2XpfXeA+RtWhifsHmN06rOEYwg5UrZBc=ZOum1Ug@mail.gmail.com>
<CA+OCxoyn32gZu78cc-p7Cr3zdsyviBVYD+X59W0-u+DTVUOzJg@mail.gmail.com>
<CAM9w-_=xKk35uQBYM+yZGkttryodU1CPF4n=jYEezKDm4GD_qw@mail.gmail.com>
<CA+OCxoxuS2GXK=LxREpniK6hHQ=d2G2FBpjD7JSohxZJpQwxPA@mail.gmail.com>
<CAM9w-_kPsWEodXtznzA0ZwXSO3d8t2agg5sUzF=2EEsGfakuMw@mail.gmail.com>
Hi
On Wed, Jun 6, 2018 at 2:02 PM, Aditya Toshniwal <aditya.toshniwal@
enterprisedb.com> wrote:
> Hi Hackers,
>
> PFA updated patch as the previous one was not working as expected. I have
> tried to make it similar to that of pgAdmin3 and you do not need to change
> client_encoding as it is set now based on server encoding. It works fine
> with "view data" also.
>
- In connection.py, at ~409, shouldn't we set the client_encoding to
SQL_ASCII? Otherwise it could be overridden with something unexpected if
the client has PGCLIENTENCODING set for example.
- With or without that change, I get the following test failure on macOS
with Python 2.7.10:
======================================================================
ERROR: runTest
(pgadmin.tools.sqleditor.tests.test_encoding_charset.TestEncodingCharset)
With Encoding SQL_ASCII
----------------------------------------------------------------------
Traceback (most recent call last):
File
"/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py",
line 86, in runTest
response = self.tester.get(url)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
line 830, in get
return self.open(*args, **kw)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/testing.py",
line 127, in open
follow_redirects=follow_redirects)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
line 803, in open
response = self.run_wsgi_app(environ, buffered=buffered)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
line 716, in run_wsgi_app
rv = run_wsgi_app(self.application, environ, buffered=buffered)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
line 923, in run_wsgi_app
app_rv = app(environ, start_response)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1997, in __call__
return self.wsgi_app(environ, start_response)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1985, in wsgi_app
response = self.handle_exception(e)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1540, in handle_exception
reraise(exc_type, exc_value, tb)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1982, in wsgi_app
response = self.full_dispatch_request()
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1614, in full_dispatch_request
rv = self.handle_user_exception(e)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1517, in handle_user_exception
reraise(exc_type, exc_value, tb)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1612, in full_dispatch_request
rv = self.dispatch_request()
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1598, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask_login.py",
line 792, in decorated_view
return func(*args, **kwargs)
File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/__init__.py",
line 576, in poll
'oids': oids
File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/ajax.py", line 61, in
make_json_response
separators=(',', ':')),
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/__init__.py",
line 399, in dumps
**kw).encode(obj)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/encoder.py",
line 291, in encode
chunks = self.iterencode(o, _one_shot=True)
File
"/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/encoder.py",
line 373, in iterencode
return _iterencode(o, 0)
UnicodeDecodeError: 'utf8' codec can't decode byte 0xad in position 0:
invalid start byte
----------------------------------------------------------------------
Ran 317 tests in 30.692s
FAILED (errors=1, skipped=21)
> The only problem is, I cannot find equivalent codec for wxConvLibc in
> python. The closest one I could find is raw_unicode_escape. So, in a
> SQL_ASCII database, non ASCII characters may differ in pgAdmin4 and
> pgAdmin3, but it will display results.
>
Yeah, I think that's fine. For the small number of people with SQL_ASCII
databases, seeing escaped characters is better than nothing.
>
>
> Dave,
> You need to add "E" before the string to be inserted, otherwise \x will be
> considered as a plain string.
> INSERT INTO sql_ascii (data) VALUES (E'[Invalid UTF-8] Blob:
> \xf4\xa5\xa3\xa5');
>
Yeah, sorry - I copied the wrong version of the query :-(
>
>
> 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 6:42 PM, Dave Page <[email protected]> wrote:
>
>> Hi
>>
>> On Tue, Jun 5, 2018 at 2:03 PM, Aditya Toshniwal <
>> [email protected]> wrote:
>>
>>> Hi
>>>
>>> On Tue, Jun 5, 2018 at 6:25 PM, Dave Page <[email protected]> wrote:
>>>
>>>>
>>>>
>>>> On Tue, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal <
>>>> [email protected]> 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
>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l300;
>>>> wxLogInfo(wxT("Setting client_encoding to '%s'")
>>>> , encoding.c_str());
>>>> 301
>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l301;
>>>> if (PQsetClientEncoding(conn, encoding.ToAscii()))
>>>> 302
>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l302;
>>>> {
>>>> 303
>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l303;
>>>> wxLogError(wxT("%s"), GetLastError().c_str());
>>>> 304
>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l304;
>>>> }
>>>> 305
>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l305;
>>>>
>>>> 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 <[email protected]> wrote:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>> On Tue, Jun 5, 2018 at 1:21 PM, Aditya Toshniwal <
>>>>>> [email protected]> wrote:
>>>>>>
>>>>>>> Hi Dave,
>>>>>>>
>>>>>>>
>>>>>>> On Tue, Jun 5, 2018 at 4:56 PM, Dave Page <[email protected]> wrote:
>>>>>>>
>>>>>>>> Hi
>>>>>>>>
>>>>>>>> On Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniwal <
>>>>>>>> [email protected]> 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 <
>>>>>>>>> [email protected]> wrote:
>>>>>>>>>
>>>>>>>>>> Hi
>>>>>>>>>>
>>>>>>>>>> On Tue, Jun 5, 2018 at 1:08 AM, Joao De Almeida Pereira <
>>>>>>>>>> [email protected]> 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
>>
>
>
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
view thread (38+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database.
In-Reply-To: <CA+OCxoz7fFQn5ADzLbohXknMNk-1kfeTjAgCO4NrsXZPRiFVNw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox