Thread: [pgAdmin4][RM#3289] Can't query SQL_ASCII database.

Hello Aditya,

It looks ok and it passes CI.

We have some recommendations:

- These look like 2 different changes so they should be in separated commits

- Do we have test coverage for the bug that you are talking about? If not we should, to ensure this problem does not happen again in a future change.

Thanks

Victoria && Joao

On Wed, May 30, 2018 at 3:06 AM Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Hackers,

PFA updated patch after all the permutations, combinations for encoding for SQL_ASCII database.  Also fixed a small glitch for sql editor connection status check.

Please note, ERROR: invalid byte sequence for encoding "UTF8": 0xe9 0x73 is a Postgres DB error and not pgAdmin4 error.

<Image Deleted>

You need to change client_encoding to the appropriate. After changing client_encoding using command - set client_encoding='XYZ', it will give not give error.

<Image Deleted>

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

On Wed, May 23, 2018 at 10:13 AM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Thank you Victoria, Anthony.

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

On Tue, May 22, 2018 at 7:15 PM, Victoria Henry <vhenry@pivotal.io> wrote:

Hi Aditya,

We made a minor change to make the patch so the python linter can pass.  Attached is the change we made.

Everything else looks good.

Sincerely,

Victoria & Anthony

On Tue, May 22, 2018 at 4:46 AM Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi,

PFA updated patch. Linter issues are fixed ( we dont have any linter setup for python :-( )

Regarding test cases, they run successfully on my system and the reason it failed for pivotal is timeout exception. I am sorry I can't help with that.

Traceback (most recent call last):
  File "/tmp/build/a453582b/pgadmin-repo/web/pgadmin/feature_tests/keyboard_shortcut_test.py", line 52, in runTest
    self._check_shortcuts()
  File "/tmp/build/a453582b/pgadmin-repo/web/pgadmin/feature_tests/keyboard_shortcut_test.py", line 77, in _check_shortcuts
    ") and contains(@class, 'open')]")
  File "/root/.pyenv/versions/pgadmin36/lib/python3.6/site-packages/selenium/webdriver/support/wait.py", line 80, in until
    raise TimeoutException(message, screen, stacktrace)
selenium.common.exceptions.TimeoutException: Message:

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

On Tue, May 22, 2018 at 1:37 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

Pivotal's buildbot is showing problems with this patch:

https://gpdb-dev.bosh.pivotalci.info/teams/pgadmin/pipelines/pgadmin-patch/jobs/run-linter/builds/66 (linter failed)

https://gpdb-dev.bosh.pivotalci.info/teams/pgadmin/pipelines/pgadmin-patch/jobs/run-tests/builds/84 (tests failed)

On Tue, May 22, 2018 at 7:05 AM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Hackers,

PFA patch for RM#3289 where decode error was thrown on querying a SQL_ASCII database table. Please note, this problem occurs only on windows.

Sample insert - insert into test_tab values ('é');

psycopg2 has a encodings dictionary where Postgres Database Encodings are mapped to python equivalent. It uses 'ascii' decoder of python to decode for SQL_ASCII encoding. If data has characters beyond the limit of ascii then it failed. The solution would be to use utf_8 decoder instead of ascii. I tried setting the client_encoding using set_client_encoding('UTF8') method of a psycopg2 connection but no luck (also its not allowed for async connection). I also tried executing "SET CLIENT_ENCODING='UTF8'" but it didn't work too.

So, as in the patch, I had to set encodings dict value directly to 'utf_8' and it seems to be working. Please note, the same is added to psycopg3 milestones

https://github.com/psycopg/psycopg2/milestone/4

Also fixed a small glitch for sql editor connection status check.

Kindly review.

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

--

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

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

Hi Victoria/Joao,

On Thu, May 31, 2018 at 2:06 AM, Joao De Almeida Pereira <jdealmeidapereira@pivotal.io> wrote:

Hello Aditya,

It looks ok and it passes CI.

We have some recommendations:

- These look like 2 different changes so they should be in separated commits

 

If you are talking of set client_encoding, then its not a bug. Its a choice given to Postgres DB user to change the encoding of the characters. Postgres will translate characters from Server Encoding to Client Encoding, and will throw error like mentioned previously. This link will help better - https://www.postgresql.org/docs/10/static/multibyte.html

The actual bug was, even after changing the client encoding to SQL_ASCII, pgAdmin4 was not able to show the output as it was failing in encoding by psycopg2. The patch is for resolving that.

 

- Do we have test coverage for the bug that you are talking about? If not we should, to ensure this problem does not happen again in a future change.

It is not possible adding test cases for encoding related stuff, as Postgres support a lot many different types of encoding and conversions (refer above link) 

Thanks

Victoria && Joao

On Wed, May 30, 2018 at 3:06 AM Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Hackers,

PFA updated patch after all the permutations, combinations for encoding for SQL_ASCII database.  Also fixed a small glitch for sql editor connection status check.

Please note, ERROR: invalid byte sequence for encoding "UTF8": 0xe9 0x73 is a Postgres DB error and not pgAdmin4 error.

<Image Deleted>

You need to change client_encoding to the appropriate. After changing client_encoding using command - set client_encoding='XYZ', it will give not give error.

<Image Deleted>

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

On Wed, May 23, 2018 at 10:13 AM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Thank you Victoria, Anthony.

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

On Tue, May 22, 2018 at 7:15 PM, Victoria Henry <vhenry@pivotal.io> wrote:

Hi Aditya,

We made a minor change to make the patch so the python linter can pass.  Attached is the change we made.

Everything else looks good.

Sincerely,

Victoria & Anthony

On Tue, May 22, 2018 at 4:46 AM Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi,

PFA updated patch. Linter issues are fixed ( we dont have any linter setup for python :-( )

Regarding test cases, they run successfully on my system and the reason it failed for pivotal is timeout exception. I am sorry I can't help with that.

Traceback (most recent call last):
  File "/tmp/build/a453582b/pgadmin-repo/web/pgadmin/feature_tests/keyboard_shortcut_test.py", line 52, in runTest
    self._check_shortcuts()
  File "/tmp/build/a453582b/pgadmin-repo/web/pgadmin/feature_tests/keyboard_shortcut_test.py", line 77, in _check_shortcuts
    ") and contains(@class, 'open')]")
  File "/root/.pyenv/versions/pgadmin36/lib/python3.6/site-packages/selenium/webdriver/support/wait.py", line 80, in until
    raise TimeoutException(message, screen, stacktrace)
selenium.common.exceptions.TimeoutException: Message:

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

On Tue, May 22, 2018 at 1:37 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

Pivotal's buildbot is showing problems with this patch:

https://gpdb-dev.bosh.pivotalci.info/teams/pgadmin/pipelines/pgadmin-patch/jobs/run-linter/builds/66 (linter failed)

https://gpdb-dev.bosh.pivotalci.info/teams/pgadmin/pipelines/pgadmin-patch/jobs/run-tests/builds/84 (tests failed)

On Tue, May 22, 2018 at 7:05 AM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Hackers,

PFA patch for RM#3289 where decode error was thrown on querying a SQL_ASCII database table. Please note, this problem occurs only on windows.

Sample insert - insert into test_tab values ('é');

psycopg2 has a encodings dictionary where Postgres Database Encodings are mapped to python equivalent. It uses 'ascii' decoder of python to decode for SQL_ASCII encoding. If data has characters beyond the limit of ascii then it failed. The solution would be to use utf_8 decoder instead of ascii. I tried setting the client_encoding using set_client_encoding('UTF8') method of a psycopg2 connection but no luck (also its not allowed for async connection). I also tried executing "SET CLIENT_ENCODING='UTF8'" but it didn't work too.

So, as in the patch, I had to set encodings dict value directly to 'utf_8' and it seems to be working. Please note, the same is added to psycopg3 milestones

https://github.com/psycopg/psycopg2/milestone/4

Also fixed a small glitch for sql editor connection status check.

Kindly review.

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

--

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

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

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?

Thanks

Victoria && Joao

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.

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

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?

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

Hi Dave,

On Tue, Jun 5, 2018 at 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 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

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 <dpage@pgadmin.org> 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 <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 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

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

 

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

 

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

 

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 <aditya.toshniwal@enterprisedb.com> wrote:

Hi Dave,

On Tue, Jun 5, 2018 at 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 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

Hi

On Tue, Jun 5, 2018 at 6:25 PM, Dave Page <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 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.

 

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 <dpage@pgadmin.org> 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 <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 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

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 <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 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 <dpage@pgadmin.org> 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 <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 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

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.

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.

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');

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 <dpage@pgadmin.org> wrote:

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 <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 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 <dpage@pgadmin.org> 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 <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 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

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 <dpage@pgadmin.org> wrote:

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 <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 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 <dpage@pgadmin.org> 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 <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 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

--

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

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

Hi Dave,

On Thu, Jun 7, 2018 at 4:07 PM, Dave Page <dpage@pgadmin.org> wrote:

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.

Yeah I agree, it would be better to add. Will add the change. 

- With or without that change, I get the following test failure on macOS with Python 2.7.10:

It works fine on my machine with Python 2.7 and macOS. Could you please let me know the Postgres DB version also.

Will test on few more machines. 

======================================================================

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 <dpage@pgadmin.org> wrote:

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 <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 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 <dpage@pgadmin.org> 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 <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 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

--

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

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