Re: Postgresql 9.4.4 - ERROR: invalid byte sequence for encoding "UTF8": 0x92

Thanks for the prompt response.  I was playing with it a bit more and seems like any character with value less than
65533is working fine, guessing that is all Unicode characters. Does the server also 
reject an insert/update when there are invalid characters? I took a character that is supposed to be invalid (displayed
asa small box, from application using 9.1 version) and pasted it in to 
application using 9.4 version of postgresql and I was able to save it to database.  Should this have failed?

If I find and fix all these characters (which would be a huge task), I want to make sure that the database is not going
totake any new invalid characters. Please let me know if there is some setting 
I can change in the configuration to do this. Another option I was thinking of is may be change the encoding of the
databaseitself to UTF8. Before the pg_restore used to fail when I tried the 
database encoding of UTF8 may be if I fix the invalid characters and then do a dump it would work.

Thanks,
Prasanth


Prasanth Reddy <dbadmin(at)nqadmin(dot)com> writes:
> I am currently running 9.1.9 and trying to upgrade to 9.4. I have done a dump and restore, when I start my java
applicationI am getting the below error. The server uses SQL_ASCII encoding and the 
> client encoding is UTF8. There are some invalid characters in the database but this has not caused a problem in the
currentversion or 9.3 (tried a restore in 9.3 and the application works fine). 

>  ERROR:  invalid byte sequence for encoding "UTF8": 0x92
>  STATEMENT:  SELECT * FROM client_data WHERE status_code = 0 ORDER BY client_name, description

You need to fix the encoding errors in your data.  9.4 is intentionally
less lax about that than prior versions.

Or, if you really want the database to be totally encoding-ignorant,
use SQL_ASCII as both client and server encoding.  But if you have the
client declared to use UTF8, the server will try not to send anything
that isn't valid UTF8.

I believe the specific change that's biting you is

    Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
    Branch: master Release: REL9_4_BR [49c817eab] 2014-02-23 15:22:50 -0500

    Plug some more holes in encoding conversion.

    Various places assume that pg_do_encoding_conversion() and
    pg_server_to_any() will ensure encoding validity of their results;
    but they failed to do so in the case that the source encoding is SQL_ASCII
    while the destination is not.  We cannot perform any actual "conversion"
    in that scenario, but we should still validate the string according to the
    destination encoding.  Per bug #9210 from Digoal Zhou.

but there were some others of the same ilk in 9.4.

            regards, tom lane
Prasanth Reddy <dbadmin(at)nqadmin(dot)com> writes:
> I am currently running 9.1.9 and trying to upgrade to 9.4. I have done a dump and restore, when I start my java
applicationI am getting the below error. The server uses SQL_ASCII encoding and the 
> client encoding is UTF8. There are some invalid characters in the database but this has not caused a problem in the
currentversion or 9.3 (tried a restore in 9.3 and the application works fine). 

>  ERROR:  invalid byte sequence for encoding "UTF8": 0x92
>  STATEMENT:  SELECT * FROM client_data WHERE status_code = 0 ORDER BY client_name, description

You need to fix the encoding errors in your data.  9.4 is intentionally
less lax about that than prior versions.

Or, if you really want the database to be totally encoding-ignorant,
use SQL_ASCII as both client and server encoding.  But if you have the
client declared to use UTF8, the server will try not to send anything
that isn't valid UTF8.

I believe the specific change that's biting you is

    Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
    Branch: master Release: REL9_4_BR [49c817eab] 2014-02-23 15:22:50 -0500

    Plug some more holes in encoding conversion.

    Various places assume that pg_do_encoding_conversion() and
    pg_server_to_any() will ensure encoding validity of their results;
    but they failed to do so in the case that the source encoding is SQL_ASCII
    while the destination is not.  We cannot perform any actual "conversion"
    in that scenario, but we should still validate the string according to the
    destination encoding.  Per bug #9210 from Digoal Zhou.

but there were some others of the same ilk in 9.4.

            regards, tom lane


On 08/11/2015 09:59 AM, Prasanth Reddy wrote:
> Hi,
>
> I have posted a question about this same issue on JDBC thinking it is a driver issue. I was told this error is
generatedby the back-end itself rather than by the driver so posting the question in 
> admin forum. See discussion on this here http://www.postgresql.org/list/pgsql-jdbc/since/201508080000/
>
> I am currently running 9.1.9 and trying to upgrade to 9.4. I have done a dump and restore, when I start my java
applicationI am getting the below error. The server uses SQL_ASCII encoding and the 
> client encoding is UTF8. There are some invalid characters in the database but this has not caused a problem in the
currentversion or 9.3 (tried a restore in 9.3 and the application works fine). 
>
>  ERROR:  invalid byte sequence for encoding "UTF8": 0x92
>  STATEMENT:  SELECT * FROM client_data WHERE status_code = 0 ORDER BY client_name, description
>
>
> org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x92
>>     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
>>     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
>>     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
>>     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
>>     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
>>     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
>>     at com.sun.rowset.JdbcRowSetImpl.execute(JdbcRowSetImpl.java:567)
>
> Same error with postgresql-9.4-1201.jdbc4.jar & postgresql-9.1-902.jdbc4.jar.
>
> Appreciate your help.
>
> Thanks,
> Prasanth
>