Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] ORDER BY different locales

From: Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general <pgsql-general(at)postgreSQL(dot)org>,pgsql-hackers <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: [HACKERS] ORDER BY different locales
Date: 2004-02-27 10:09:40
Message-ID: 20040227100940.GA17178@zf.jcu.cz (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Thu, Feb 26, 2004 at 09:16:03AM -0500, Tom Lane wrote:
> Karel Zak <zakkr(at)zf(dot)jcu(dot)cz> writes:
> >  I  think possible  solution is  special function  used ORDER  BY clause
> >  which knows to switch by safe  way to wanted locales, convert string by
> >  strxfrm() and switch back to backend locales.
> 
> This function breaks the whole backend if an elog() failure occurs while

 Fixed by sigsetjmp(Warn_restart..). I hope it's more safe now.

> it's got the wrong locale set.  I believe it would also be remarkably
> slow --- doesn't setlocale() involve reading a new locale definition

 You're right, it's slow. But sometimes  is more important that it works
 and not all queries work with thousands records like my test below.

> I think the ultimate solution to our multi-locale problems will have to
> involve abandoning the C library's support functions and writing locale
> support that allows multiple locale-defining structures referenced by

 Agree. But as you said it's huge task and I think if it won't implement
 in 7.5 we  can add nls_string() to the  contrib tree. BTW, nls_string()
 is  "product" of  Czech database  list  where Oracle  users have  still
 problems with PostgreSQL ;-)

 Latest version:
 ftp://ftp2.zf.jcu.cz/users/zakkr/pg/postgresql-nls-string-0.52.tar.gz

 Note, I  add "CC:"  to pgsql-general, maybe  it's interesting  for some
 normal users too.

 Tests:

        # SELECT count(*) FROM nlstest;
         count  
        --------
         100000

        # SELECT data FROM nlstest ORDER BY upper(data) DESC LIMIT 1;
        Time: 1213.87 ms


        # SELECT data FROM nlstest ORDER BY nls_string(data, 'en_US') LIMIT 1;
        Time: 4269.00 ms


    Karel

-- 
 Karel Zak  <zakkr(at)zf(dot)jcu(dot)cz>
 http://home.zf.jcu.cz/~zakkr/

In response to

pgsql-hackers by date

Next:From: Zeugswetter Andreas SB SDDate: 2004-02-27 10:15:27
Subject: Re: Thread safe connection-name mapping in ECPG. Is it
Previous:From: Shridhar DaithankarDate: 2004-02-27 09:32:22
Subject: Re: Thread safe connection-name mapping in ECPG. Is it

pgsql-general by date

Next:From: Bas ScheffersDate: 2004-02-27 10:32:26
Subject: Re: Help with a query
Previous:From: Karam ChandDate: 2004-02-27 09:19:06
Subject: Re: Windows Library for libpq

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group