SEO

Hi,

I have set up a postgreSQL on a x86 Solaris 10 box with compiling the source code 8.1.4. My application is need to store the characters encoding with GB18030 or GBK. My Solaris box is installed the locale code zh_CN.GB18030 but I fail to initdb -E GB18030 so I have to use the EUC_CN to initdb and create database.

I have published my application since a month and I notice the appcation server report a few jdbc errors that says :
org.postgresql.util.PSQLException: ERROR: character 0xe28094 of encoding "UTF8" has no equivalent in "EUC_CN"
I think the application user input some kind of characters out of range EUC_CN.

I change my developing test db server to UTF-8 use the initdb --encoding=UTF-8 and recreate the testing database with encoding utf-8. And this testing db server is host on a windows box the encoding is 936(GBK). I restore the data from the pg_dump file. It's fine to work and the sort order is fine also. I mean the Chinese GB18030 data column is "order by" correctly. But when i do the same thing on the Solaris box. I found the sort order is worng. I can fix this with a convert function like this: select cname from t_resume order by convert(cname using utf8_to_gb18030); on the Solaris box. After I use the convert function the order is correct for the Chinese characters.

I think I can change all of my sql in the application to fit this behavior. But when i run the same sql on the windows box. I get the error message: ERROR: could not convert string to UTF-16: error 1113. I stick on it!

I dig on the web and this mail archives but nothing helps. I take a look of the 8.2 beta document in the section "Character Set Support". And I find the table of "Server Character Sets" add a new column Server Support, but the GBK and GB18030 is "not support".

So, how can I fix this issue? any ideas? Thank you everyone.



Steve Yao stevegy (at) 126 (dot) com

On Tue, Oct 03, 2006 at 04:16:36PM +0800, stevegy wrote:
> I have published my application since a month and I notice the appcation server report a few jdbc errors that says :
> org.postgresql.util.PSQLException: ERROR: character 0xe28094 of encoding "UTF8" has no equivalent in "EUC_CN"
> I think the application user input some kind of characters out of range EUC_CN.

That's what the message implies, yes.

> I change my developing test db server to UTF-8 use the initdb
> --encoding=UTF-8 and recreate the testing database with encoding
> utf-8. And this testing db server is host on a windows box the
> encoding is 936(GBK). I restore the data from the pg_dump file. It's
> fine to work and the sort order is fine also. I mean the Chinese
> GB18030 data column is "order by" correctly. But when i do the same
> thing on the Solaris box. I found the sort order is worng. I can fix
> this with a convert function like this: select cname from t_resume
> order by convert(cname using utf8_to_gb18030); on the Solaris box.
> After I use the convert function the order is correct for the Chinese
> characters.

Sortigng is provided by the OS. If your OS can't sort UTF-8, postgres
won't either. But you must make sure that the encoding you specify
during install matches the locale, otherwise funny things happen. You
can't just pick a locale and encoding and expect it to work.

My guess is you have some incompatability there.

> I think I can change all of my sql in the application to fit this
> behavior. But when i run the same sql on the windows box. I get the
> error message: ERROR: could not convert string to UTF-16: error 1113.
> I stick on it!

Windows has yet another way of sorting. Again, make sure the locale
selected matches the encoding you select.

> So, how can I fix this issue? any ideas? Thank you everyone.

Find the locales you're using and make sure they match the encoding
everywhere...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFImrdIB7bNG8LQkwRAhkQAJ9wV8R3CeSElFc0Ugl18X a7fwn/sgCeMAbI
nItAWvkQcBCdpxouAQEllI8=
=mxE4
-----END PGP SIGNATURE-----

Hi,

Thank you for your reply.
I guess you mean right. I am continuing find the solution now.
I print all the current locale on my solaris here:
=======================================
-bash-3.00# locale
LANG=zh_CN.GB18030
LC_CTYPE="zh_CN.GB18030"
LC_NUMERIC="zh_CN.GB18030"
LC_TIME="zh_CN.GB18030"
LC_COLLATE="zh_CN.GB18030"
LC_MONETARY="zh_CN.GB18030"
LC_MESSAGES="zh_CN.GB18030"
LC_ALL=
=======================================
My testing database is initdb -E UTF8. And i guess the solaris can not handle the UTF-8 sorting with the LC_COLLATE="zh_CN.GB18030". But i needto prove this.
So i plan to change the locale of my solaris box. I have installed the zh_CN.UTF8 on it. I am looking for a way to change the solaris locale. If this changing need to re-boot machine that I should do this in some not busy time for the live application.

I think I post 2 questions in one post and it's a bad idea. So I post another mail for the convert function error.

Thank you and have a nice day.


-- Steve Yao




-----Ô*ʼÓʼþ-----
·¢¼þÈË:"Martijn van Oosterhout"
·¢ËÍʱ¼ä:2006-10-03 21:51:25
ÊÕ¼þÈË:"stevegy"
³*ËÍgsql-general@postgresql.org
Ö÷Ìâ:Re: [GENERAL] Hi, everyone. can i ask a question for th e server side encoding GB18030 and sort order?


On Tue, Oct 03, 2006 at 04:16:36PM +0800, stevegy wrote:

> I have published my application since a month and I notice the appcation server report a few jdbc errors that says :

> org.postgresql.util.PSQLException: ERROR: character 0xe28094 of encoding "UTF8" has no equivalent in "EUC_CN"

> I think the application user input some kind of characters out of rangeEUC_CN.



That's what the message implies, yes.



> I change my developing test db server to UTF-8 use the initdb

> --encoding=UTF-8 and recreate the testing database with encoding

> utf-8. And this testing db server is host on a windows box the

> encoding is 936(GBK). I restore the data from the pg_dump file. It's

> fine to work and the sort order is fine also. I mean the Chinese

> GB18030 data column is "order by" correctly. But when i do the same

> thing on the Solaris box. I found the sort order is worng. I can fix

> this with a convert function like this: select cname from t_resume

> order by convert(cname using utf8_to_gb18030); on the Solaris box.

> After I use the convert function the order is correct for the Chinese

> characters.



Sortigng is provided by the OS. If your OS can't sort UTF-8, postgres

won't either. But you must make sure that the encoding you specify

during install matches the locale, otherwise funny things happen. You

can't just pick a locale and encoding and expect it to work.



My guess is you have some incompatability there.



> I think I can change all of my sql in the application to fit this

> behavior. But when i run the same sql on the windows box. I get the

> error message: ERROR: could not convert string to UTF-16: error 1113.

> I stick on it!



Windows has yet another way of sorting. Again, make sure the locale

selected matches the encoding you select.



> So, how can I fix this issue? any ideas? Thank you everyone.



Find the locales you're using and make sure they match the encoding

everywhere...



Have a nice day,

--

Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

> From each according to his ability. To each according to his ability tolitigate.

On Wed, Oct 04, 2006 at 07:50:12AM +0800, stevegy wrote:
> My testing database is initdb -E UTF8. And i guess the solaris can
> not handle the UTF-8 sorting with the LC_COLLATE="zh_CN.GB18030". But
> i need to prove this.

A locale can only handle one charset, usually the one given by "locale
charset". So if it handles sorting in GB18030 then by definition it
can't handle UTF-8.

> So i plan to change the locale of my solaris box. I have installed
> the zh_CN.UTF8 on it. I am looking for a way to change the solaris
> locale. If this changing need to re-boot machine that I should do
> this in some not busy time for the live application.

The locale is not a global setting. If you set the LANG or LC_ALL
variable, it will change the locale of any program run with that
environment variable. The default is the "C" locale.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFI4HoIB7bNG8LQkwRAmqsAJ4xZ7FQuqEP4OpgNTAcl4 VQlGPYSACgiu4Q
nAbO57xfI//ahiKIfYhswBY=
=xjy5
-----END PGP SIGNATURE-----

Hi Martijn,

Thank you for the reply.

I think the locale settings is a diffrent in my case, and in the logical way, I guess the locale environs should be used by the program in the system C run-time library for the string operating functions such like comparing or something else like that.

I have login as user name 'postgres' and changed the LANG=zh_CN.UTF-8; export LANG and then the locale command output this:
========================================
-bash-3.00$ locale
LANG=zh_CN.UTF-8
LC_CTYPE="zh_CN.UTF-8"
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_ALL=
========================================
So, now i stop the postgres: pg_ctl stop -D ./data_euc_cn and start it use the UTF-8 encoding data directory: pg_ctl start -D ./data_utf8. But thesort result for the Chinese characters is still wrong. And I notice thatthe sort result is diffrent from the LANG=zh_CN.GB18030.

I think I still have not a direct way to solve this. Or, maybe, the postgres uses the locale settings from the other than the current postgres user?
Any suggestions?


-- Steve Yao



-----Ô*ʼÓʼþ-----
·¢¼þÈË:"Martijn van Oosterhout"
·¢ËÍʱ¼ä:2006-10-04 17:42:00
ÊÕ¼þÈË:"stevegy"
³*ËÍgsql-general@postgresql.org
Ö÷Ìâ:Re: [GENERAL] Hi, ever


On Wed, Oct 04, 2006 at 07:50:12AM +0800, stevegy wrote:

> My testing database is initdb -E UTF8. And i guess the solaris can

> not handle the UTF-8 sorting with the LC_COLLATE="zh_CN.GB18030". But

> i need to prove this.



A locale can only handle one charset, usually the one given by "locale

charset". So if it handles sorting in GB18030 then by definition it

can't handle UTF-8.



> So i plan to change the locale of my solaris box. I have installed

> the zh_CN.UTF8 on it. I am looking for a way to change the solaris

> locale. If this changing need to re-boot machine that I should do

> this in some not busy time for the live application.



The locale is not a global setting. If you set the LANG or LC_ALL

variable, it will change the locale of any program run with that

environment variable. The default is the "C" locale.



Have a nice day,

--

Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

> From each according to his ability. To each according to his ability tolitigate.

On Wed, Oct 04, 2006 at 09:45:26PM +0800, stevegy wrote:
> So, now i stop the postgres: pg_ctl stop -D ./data_euc_cn and start
> it use the UTF-8 encoding data directory: pg_ctl start -D
> ./data_utf8. But the sort result for the Chinese characters is still
> wrong. And I notice that the sort result is diffrent from the
> LANG=zh_CN.GB18030.

You need to do more to change the encoding of a database. The encoding
is fixed at cluster-creation time, so you need to run initdb again to
actually change the locale/encoding.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFI8a9IB7bNG8LQkwRAjtrAJ43OnbxDqeGSRo8uxB9vf PSSL6xvACcCDIc
mxWL9sZAy75TeZaMAvIGf80=
=GNaY
-----END PGP SIGNATURE-----

Hi Martijn,

I have changed the locale to LANG=zh_CN.UTF-8;export LANG first, stop the pgsql, and issue the command:
initdb -D ./data -E UTF8 --locale=zh_CN.UTF-8 --lc-collate=zh_CN.UTF-8 -U postgres -W
so i get a fresh new data cluster in this ./data, then I pg_ctl start -D ./data, psql to create the utf8 encoding database:

CREATE DATABASE "Recruit"
WITH OWNER = recruit
ENCODING = 'UTF-8'; -- the owner has been created before this sql

pg_restore -d Recruit ./backup/r2.tar

psql and \c Recruit
\encoding gb18030
-- i will not read the Chinese characters in client mode without this setting on my solaris 10, maybe there's something about the font mapping.

select cname from t_resume order by cname;
and then i get the wrong order result.

I really want to know why the server side encoding for GB18030 is not supported?


Thank you.



-- Steve Yao



-----Ô*ʼÓʼþ-----
·¢¼þÈË:"Martijn van Oosterhout"
·¢ËÍʱ¼ä:2006-10-04 22:35:41
ÊÕ¼þÈË:"stevegy"
³*ËÍ:"pgsql-general@postgr"
Ö÷Ìâ:Re: [GENERAL] Hi,&nbs


On Wed, Oct 04, 2006 at 09:45:26PM +0800, stevegy wrote:

> So, now i stop the postgres: pg_ctl stop -D ./data_euc_cn and start

> it use the UTF-8 encoding data directory: pg_ctl start -D

> ./data_utf8. But the sort result for the Chinese characters is still

> wrong. And I notice that the sort result is diffrent from the

> LANG=zh_CN.GB18030.



You need to do more to change the encoding of a database. The encoding

is fixed at cluster-creation time, so you need to run initdb again to

actually change the locale/encoding.



Hope this helps,

--

Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

> From each according to his ability. To each according to his ability tolitigate.

"stevegy" <stevegy@126.com> writes:
> I really want to know why the server side encoding for GB18030 is not supported?

Because it overlaps the ASCII character set, ie, it has multibyte
characters in which some of the bytes don't have the high bit set.
That creates too many parsing risks for us to be willing to deal
with it inside the backend.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hi Tom,

Thank you for your reply.

I think the character map is a problem. So I see the choices for the Chinese user is:
1. use the EUC_CN
2. use the UTF-8
The first choice is what i'm using and the problem is clear to me. I can not store some special characters into the PostgreSQL with this encoding because the character is out of range.
The second what i am testing now still in some strange issue for the sorting order.

So, any other choice for continuing use the PostgreSQL in my situation?


-- Steve Yao






-----Ô*ʼÓʼþ-----
·¢¼þÈË:"Tom Lane"
·¢ËÍʱ¼ä:2006-10-05 09:50:56
ÊÕ¼þÈË:"stevegy"
³*ËÍ:"martijn van oosterhout" ,"pgsql-general@postgr"
Ö÷Ìâ:Re: [GENERAL] H


"stevegy" <stevegy@126.com> writes:
> I really want to know why the server side encoding for GB18030 is not supported?

Because it overlaps the ASCII character set, ie, it has multibyte
characters in which some of the bytes don't have the high bit set.
That creates too many parsing risks for us to be willing to deal
with it inside the backend.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hi Martijn,

I had changed my Solaris locale setting to zh_CN.UTF-8. I modified the /etc/default/init LANG=zh_CN.UTF-8 and reboot. initdb a new database usedthe encoding UTF8, but the sort order is wrong on the Solaris.

I dig into the TODO list on the postgreSQL web site. The Multi-Language Support ssection I found some resource like this: http://archives.postgresql.org/pgsql...9/msg00662.php

I guess the encoding of UTF-8 support is not stable right now in PostgreSQL. But, on my windows box, 8.1.4 handle the utf8 sorting good. What can I do now? waiting for some kind of patch?


-- Steve Yao






-----Ô*ʼÓʼþ-----
·¢¼þÈË:"Martijn van Oosterhout"
·¢ËÍʱ¼ä:2006-10-04 17:42:00
ÊÕ¼þÈË:"stevegy"
³*ËÍgsql-general@postgresql.org
Ö÷Ìâ:Re: [GENERAL] Hi, ever


On Wed, Oct 04, 2006 at 07:50:12AM +0800, stevegy wrote:

> My testing database is initdb -E UTF8. And i guess the solaris can

> not handle the UTF-8 sorting with the LC_COLLATE="zh_CN.GB18030". But

> i need to prove this.



A locale can only handle one charset, usually the one given by "locale

charset". So if it handles sorting in GB18030 then by definition it

can't handle UTF-8.



> So i plan to change the locale of my solaris box. I have installed

> the zh_CN.UTF8 on it. I am looking for a way to change the solaris

> locale. If this changing need to re-boot machine that I should do

> this in some not busy time for the live application.



The locale is not a global setting. If you set the LANG or LC_ALL

variable, it will change the locale of any program run with that

environment variable. The default is the "C" locale.



Have a nice day,

--

Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

> From each according to his ability. To each according to his ability tolitigate.