sort order (ORDER BY) hu_HU.UTF-8 locale with UTF-8 encoding is not working correctly on FreeBSD

Dear List,

I'm trying to solve this problem for more than 2 days, but I have no
other idea.

My system is:

FreeBSD titanium 8.1-RELEASE FreeBSD 8.1-RELEASE #0: Mon Jul 19 02:36:49
UTC 2010 root@mason.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC amd64

Tested PgSQL versions are: 8.4 and 9.0.3 (fresh install using ports)

initdb flags are: --encoding=UTF-8 --locale=hu_HU.UTF-8

Initdb output:

The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale hu_HU.UTF-8.
The default text search configuration will be set to "hungarian".

creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 40
selecting default shared_buffers ... 28MB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

step 1: createdb -U wooh test --encoding=UTF-8 --locale=hu_HU.UTF-8

step 2: psql -U wooh -l
                              List of databases
   Name    | Owner | Encoding |  Collation  |    Ctype    | Access
privileges
-----------+-------+----------+-------------+-------------+-------------------
 postgres  | pgsql | UTF8     | hu_HU.UTF-8 | hu_HU.UTF-8 |
 template0 | pgsql | UTF8     | hu_HU.UTF-8 | hu_HU.UTF-8 | =c/pgsql
     +
           |       |          |             |             | pgsql=CTc/pgsql
 template1 | pgsql | UTF8     | hu_HU.UTF-8 | hu_HU.UTF-8 | =c/pgsql
     +
           |       |          |             |             | pgsql=CTc/pgsql
 test      | wooh  | UTF8     | hu_HU.UTF-8 | hu_HU.UTF-8 |

Everything seems to be OK.

let's create a table with idn, and name.

step 3:

test=# create table test (idn serial primary key not null, name
varchar(255));
NOTICE: CREATE TABLE will create implicit sequence "test_idn_seq" for
serial column "test.idn"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE

step 4:

[root@titanium /home/wooh]# file insert.sql
insert.sql: UTF-8 Unicode text

step 5:

[root@titanium /home/wooh]# cat insert.sql
INSERT INTO test (name) values ( 'a' );
INSERT INTO test (name) values ( 'ďż˝' );
INSERT INTO test (name) values ( 'b' );
INSERT INTO test (name) values ( 'c' );
INSERT INTO test (name) values ( 'd' );
INSERT INTO test (name) values ( 'e' );
INSERT INTO test (name) values ( 'ďż˝' );
INSERT INTO test (name) values ( 'f' );
INSERT INTO test (name) values ( 'g' );
INSERT INTO test (name) values ( 'h' );

step 6:

[root@titanium /home/wooh]# psql -U wooh test < insert.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

step 7:

[root@titanium /home/wooh]# psql -U wooh test
psql (9.0.3)
Type "help" for help.

test=# select * from test order by name asc;
idn | name
-----+------
1 | a
3 | b
4 | c
5 | d
6 | e
8 | f
9 | g
10 | h
2 | ďż˝
7 | ďż˝
(10 rows)

test=# show lc_collate;
lc_collate
-------------
hu_HU.UTF-8
(1 row)

test=# show lc_ctype;
lc_ctype
-------------
hu_HU.UTF-8
(1 row)

The sort order is incorrect, and I cannot find out why. Everything seems
to be Ok. (locale, collate, encoding)

But this whole thing works if I chose "LATIN2" encoding with
"hu_HU.ISO8859-2" locale. Unfortunately we use UTF-8 databases, so I
need to use the UTF8 Encoding with hu_HU.UTF-8

Any ideas?

Additional information:

[root@titanium /home/wooh]# pg_controldata /usr/local/pgsql/data/
pg_control version number: 903
Catalog version number: 201008051
Database system identifier: 5570517093231621070
Database cluster state: in production
pg_control last modified: Sun Feb 6 11:19:27 2011
Latest checkpoint location: 0/1610CA0
Prior checkpoint location: 0/15F8028
Latest checkpoint's REDO location: 0/1610CA0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/675
Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 654
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Time of latest checkpoint: Sun Feb 6 11:19:19 2011
Minimum recovery ending location: 0/0
Backup start location: 0/0
Current wal_level setting: minimal
Current max_connections setting: 40
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value

--
Adam PAPAI
BSD Support Service
http://www.bsdsupportservice.hu
E-mail: adam.papai@bsdsupportservice.hu
Phone: +36 30 33-55-735 (Hungary)