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

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)