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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-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/
From | Date | Subject | |
---|---|---|---|
Next Message | Bas Scheffers | 2004-02-27 10:32:26 | Re: Help with a query |
Previous Message | Karam Chand | 2004-02-27 09:19:06 | Re: Windows Library for libpq |
From | Date | Subject | |
---|---|---|---|
Next Message | Zeugswetter Andreas SB SD | 2004-02-27 10:15:27 | Re: Thread safe connection-name mapping in ECPG. Is it |
Previous Message | Shridhar Daithankar | 2004-02-27 09:32:22 | Re: Thread safe connection-name mapping in ECPG. Is it |