Обсуждение: Unicode vs SQL_ASCII DBs

Hi

I need to store accented characters in a postgres (7.4) database, and
access the data (mostly) using the postgres JDBC driver (from a web app).

Does anyone know if:

1) Is there a performance loss using (multibyte) UNICODE vs (single byte)
SQL_ASCII/LATINxxx character encoding? (In terms of extra data, and
searching/sorting speeds).

2) Can SQL_ASCII be used for accented characters.

3) If I want accented characters to sort correctly, must I select UNICODE
(or the appropriate ISO 8859 char set) over SQL_ASCII?

4) I'm not initially expecting arabic, chinese, cyrillic or other language
types to be stored in the database. But if they were, would UNICODE be the
best encoding scheme to use for future proofing the data?

5) If I decide not to support/store non-latin languages, is it better to
use a LATIN encoding over UNICODE?

6) What is MULE_INTERNAL? Does it offer performance advantages?

[This next question probably belongs to the JDBC list, but I'll ask anyway]

7) Because the database is being used to backend a java web application,
are there other issues that I need to be aware of, for example, do I have
to convert all data received to UTF-8 before writing it into the database?
And do I have to ensure that the response (from the webserver)
content-type is always set to UTF-8 to be rendered correctly in a user's
browser?

Thanks for any help/advice.

John Sidney-Woollett

ps I did some tests between two databases; once created using UNICODE
encoding, and the other using SQL_ASCII encoding. The database initdb
command specified no encoding, so I guess that SQL_ASCII is the default
encoding. The results are below:

I created the following table in two databases:

            Table "public.table1"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 id     | integer                | not null
 data   | character varying(255) |

Here are the databases; one with UNICODE encoding, and the other with
SQL_ASCII encoding.

          List of databases
     Name     |  Owner   | Encoding
--------------+----------+-----------
 johntest     | postgres | UNICODE
 johntest2    | postgres | SQL_ASCII
 template0    | postgres | SQL_ASCII
 template1    | postgres | SQL_ASCII

I inserted the data into both databases in psql (first setting the client
encoding to LATIN1). Here is the data (the funny character is the word
"test" with the e replaced by an e acute (233).

set client_encoding to 'LATIN1';

insert into table1 values (1, 'tést');
insert into table1 values (2, 'tast');
insert into table1 values (3, 'tost');
insert into table1 values (4, 'test');

Now select the data ordered

(UNICODE DB): select * from table1 order by data;
 id | data
----+------
  2 | tast
  4 | test
  1 | tést
  3 | tost

(SQL_ASCII DB): select * from table1 order by data;
 id | data
----+------
  2 | tast
  4 | test
  3 | tost
  1 | tést

NOTE: Bad ordering using SQL_ASCII...

Now I did some other tests, I set the client encoding to UNICODE, and
retrieved the data from both databases:

set client_encoding to 'UNICODE';

(SQL_ASCII DB): select * from table1 order by data;
 id | data
----+------
  2 | tast
  4 | test
  3 | tost
  1 | tst

NOTE: You can see that the e-acute has been "lost"...

 (UNICODE DB): select * from table1 order by data;
 id | data
----+------
  2 | tast
  4 | test
  1 | tést
  3 | tost

On Sat, 31 Jan 2004, John Sidney-Woollett wrote:

> Hi
>
> I need to store accented characters in a postgres (7.4) database, and
> access the data (mostly) using the postgres JDBC driver (from a web app).
>
> Does anyone know if:
>
> 2) Can SQL_ASCII be used for accented characters.

Not with the JDBC driver.  A client which is blissfully unaware of
encoding issues can pass data into and out of an ascii db without knowing
what the encoding is, but java must know.

>
> 3) If I want accented characters to sort correctly, must I select UNICODE
> (or the appropriate ISO 8859 char set) over SQL_ASCII?

You are confusing encoding with locale.  Locales determines the correct
sort order and you must choose an encoding that works with your locale.

>
> 4) I'm not initially expecting arabic, chinese, cyrillic or other language
> types to be stored in the database. But if they were, would UNICODE be the
> best encoding scheme to use for future proofing the data?

Yes.

> 7) Because the database is being used to backend a java web application,
> are there other issues that I need to be aware of, for example, do I have
> to convert all data received to UTF-8 before writing it into the database?
> And do I have to ensure that the response (from the webserver)
> content-type is always set to UTF-8 to be rendered correctly in a user's
> browser?

The jdbc driver will correctly handle conversions between the database
encoding and the encoding the jvm is run under.  Receiving data from a web
application is a little different because you must convert data from the
client's encoding to the jvm's encoding for this to work.  The simplest
way to do this is just to make sure that you are using unicode in all
three places (server,jvm, and client).

Other things to note:

LOWER()/UPPER() only work correctly in a single byte encoding (not
unicode)

If using binary data (bytea) via JDBC you may need to use a unicode
db.  I don't know if this has been fixed, but the server would attempt to
do an encoding conversion on the binary data:

http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00045.php

Kris Jurka

On Jan 31, 2004, at 7:32 AM, John Sidney-Woollett wrote:
>
> 4) I'm not initially expecting arabic, chinese, cyrillic or other
> language
> types to be stored in the database. But if they were, would UNICODE be
> the
> best encoding scheme to use for future proofing the data?
>

If there is a remote chance that you might ever need CJK support you
should start now with unicode encoding.  Switching the encoding of your
database is a major, major pain.  I have a SQL_ASCII database that I
wish were UNICODE but the conversion is such an ugly undertaking that I
haven't even tried.

culley

Kris, thanks for you feedback. Can you give me any further info on the
questions below?

Kris Jurka said:
>> 3) If I want accented characters to sort correctly, must I select
>> UNICODE
>> (or the appropriate ISO 8859 char set) over SQL_ASCII?
>
> You are confusing encoding with locale.  Locales determines the correct
> sort order and you must choose an encoding that works with your locale.

Except that in my test, the two differently encoded databases were in the
same 7.4.1 cluster with the same locale, yet they sorted the *same* data
differently - implying the encoding is a factor.

Any idea why would that be?

here is output from pg_controldata:

pg_control version number:            72
Catalog version number:               200310211
Database cluster state:               in production
pg_control last modified:             Mon 02 Feb 2004 11:21:29 GMT
Current log file ID:                  0
Next log file segment:                2
Latest checkpoint location:           0/124B958
Prior checkpoint location:            0/1149DFC
Latest checkpoint's REDO location:    0/124B958
Latest checkpoint's UNDO location:    0/0
Latest checkpoint's StartUpID:        16
Latest checkpoint's NextXID:          527327
Latest checkpoint's NextOID:          26472
Time of latest checkpoint:            Mon 02 Feb 2004 11:21:27 GMT
Database block size:                  8192
Blocks per segment of large relation: 131072
Maximum length of identifiers:        64
Maximum number of function arguments: 32
Date/time type storage:               floating-point numbers
Maximum length of locale name:        128
LC_COLLATE:                           en_GB.UTF-8
LC_CTYPE:                             en_GB.UTF-8

and

     Name      |  Owner   | Encoding
---------------+----------+-----------
 johntest      | postgres | UNICODE
 johntest2     | postgres | SQL_ASCII
 template0     | postgres | SQL_ASCII
 template1     | postgres | SQL_ASCII

> Other things to note:
>
> LOWER()/UPPER() only work correctly in a single byte encoding (not
> unicode)

Are there any other gotchas that I need to be aware of with a UNICODE
encoded database?

I save mention by Tom Lane of a bug: [QUOTE] The bug turns out not to be
Fedora-specific at all.  I believe it will happen on any platform if you
are using both a multibyte database encoding (such as Unicode) *and* a
non-C locale. PG 7.4 has a more restricted form of the bug --- it's not
locale specific but does still require a multibyte encoding. [END QUOTE]

I basically need "english" sorting, and accented character support without
any JDBC access/conversion problems. Do you think that my current DB
locale (en_GB.UTF-8) and UNICODE encoded database the best solution? Or
can you suggest something better?

Thanks

John Sidney-Woollett

On Mon, 2 Feb 2004, John Sidney-Woollett wrote:

> Kris, thanks for you feedback. Can you give me any further info on the
> questions below?
>
> Kris Jurka said:
> >> 3) If I want accented characters to sort correctly, must I select
> >> UNICODE
> >> (or the appropriate ISO 8859 char set) over SQL_ASCII?
> >
> > You are confusing encoding with locale.  Locales determines the correct
> > sort order and you must choose an encoding that works with your locale.
>
> Except that in my test, the two differently encoded databases were in the
> same 7.4.1 cluster with the same locale, yet they sorted the *same* data
> differently - implying the encoding is a factor.

Right, note the "and you must choose an encoding that works with your
locale." clause.  A SQL_ASCII encoding and a UTF-8 locale don't work.

> I basically need "english" sorting, and accented character support without
> any JDBC access/conversion problems. Do you think that my current DB
> locale (en_GB.UTF-8) and UNICODE encoded database the best solution? Or
> can you suggest something better?

If you need "english" sorting like "en_GB" then that is the best option,
but if you just need regular sorting the C locale might be better.  It is
sometimes confusing how en_US (I assume GB is similar) sorts strings with
spaces and punctuation and so on.

Kris Jurka

Kris Jurka said:
> If you need "english" sorting like "en_GB" then that is the best option,
> but if you just need regular sorting the C locale might be better.  It is
> sometimes confusing how en_US (I assume GB is similar) sorts strings with
> spaces and punctuation and so on.

If I switch from "en_GB" locale to "C" locale (by recreating the cluster
using "initdb --no-locale"), will I still get accented characters be
sorted correctly, ie tast, test, tést, tost (if the DB encoding is
UNICODE)?

From what I've read, the "C" locale will give me better performance and
optimization for certain functions/expressions (eg "like").

Do you think that the "C" locale (which affects the LC_CTYPE setting) will
allow me to assume that searching and sorting will operate as I
hope/expect, and that all my existing functions etc will work as expected?

> LOWER()/UPPER() only work correctly in a single byte encoding (not
> unicode)

In one pl/pgsql function, I need the ability to lowercase (LOWER) the
three character file extension of a filename stored in the datbase. Will
the LOWER call do nothing with a char/varchar object in a unicode
database?

If so, is there somekind of workaround - I "know" the file extensions that
I'm interested in - can I replace the call to LOWER by a *huge* if then
elsif block of code which does a string match to workout the replacement
extension (in lowercase)?

Thanks again for your patience and help

John Sidney-Woollett

ps I will do some more testing, but with this topic I'm groping around in
the dark...

Kris Jurka <books@ejurka.com> writes:
> On Mon, 2 Feb 2004, John Sidney-Woollett wrote:
>> Except that in my test, the two differently encoded databases were in the
>> same 7.4.1 cluster with the same locale, yet they sorted the *same* data
>> differently - implying the encoding is a factor.

> Right, note the "and you must choose an encoding that works with your
> locale." clause.  A SQL_ASCII encoding and a UTF-8 locale don't work.

In practice, any given locale setting assumes a particular encoding and
will not work if some other encoding is used.  For instance, on recent
Red Hat releases:

$ locale -a | grep ^de_DE
de_DE
de_DE.iso88591
de_DE.iso885915@euro
de_DE.utf8
de_DE.utf8@euro
de_DE@euro

I'm not too sure which encoding "de_DE" uses, but the other two are
clearly named to reflect their expected encoding.

It is really a bug that PG allows you to select incompatible locale and
encoding settings.  We'd fix it if we could figure out a portable way of
determining which encoding a locale expects --- unfortunately the
standard APIs for libc omit this information ...

            regards, tom lane