Patches UTF-8 Collation under FreeBSD

So, today I will try to answer the popular problem of how to patch KDE UTF-8 under FreeBSD.

At one point, transferring a database from a test server to a production to prepare a beta version, I hit my head against the problem with UTF-8 strings in PostgreSQL. Or rather, I stumbled upon PostgreSQL’s complete reluctance to issue a SELECT upper ('gopher') for the SUSLIK state as requested. As a result of the search, I was shocked by the bugs, because I didn’t expect from modern OSes such completely childish blunders!

PostgreSQL collected from ports in the fryah for working with unicode strings uses the system LC_COLLATE. In order to understand what the rake I needed was just one command:

 $ ls -l /usr/share/locale/en_US.UTF-8/LC_COLLATE
 lrwxr-xr-x 1 root wheel 28 Dec 3 23:30 /usr/share/locale/ru_RU.UTF-8/
 LC_COLLATE -> ../la_LN.US-ASCII/LC_COLLATE

Moving away from the shock, I got into Google, looking for a recipe for the treatment of this outrage. I propose it to you:
')

1. First you need to patch KDE UTF-8 under FreeBSD (my thanks to the author of the solution ):
   
   

    wget http://blog.lexa.ru/files/ru_RU.UTF-8.LC_COLLATE.tar.gz
    tar xzvf_US.UTF-8.LC_COLLATE.tar.gz
    cd en_US.UTF-8.LC_COLLATE
    make freebsdsort or make universal
    su
    make install
   

   
   
2. But the problem did not end there. If your PostgreSQL is initialized in a different encoding from ru_RU.UTF-8, then this patch will not be enough. It will be necessary to re-initialize the database (which, of course, absolutely did not make me happy).
   
3. Make a dump of our database (pg_dump or pg_dumpall).
   
4. Stop PostgresSQL:
   
   

    /usr/local/etc/rc.d/postgresql stop
   

   
   
5. Add the following lines to the /etc/login.conf file:
   
   

    postgres: \
    : lang = ru_RU.UTF-8: \
    : setenv = LC_COLLATE = C: \
    : tc = default:
   

   
   
6. Add the flag to the /etc/rc.conf file for proper initialization:
   
   

    postgresql_initdb_flags = "- locale = en_RU.UTF-8"
   

   
   
7. Initialize the database:
   
   

    /usr/local/etc/rc.d/postgresql initdb
   

   
   
8. Fill back our base. Voila!
   

And a few more comments about ...

• The bayan seems to be quite old, since the ticket on this issue was 2 years old not so long ago :). And the first mentions are dated at all 2005 year.
  
• Speed: (Unfortunately, the speed of sorting UTF-8 strings using this patch is 10 times slower than, for example, sorting in the ru_RU.KOI8-R locale, and almost 30 times slower than in the C locale.
  

PS Crosspost from a personal blog.