Re: How to add locale support for each column? - Mailing list pgsql-hackers

Greg Stark <gsstark@mit.edu> writes:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> 2) switching the locale at run time is too expensive when using the system
>> library.

> Fwiw I did some experiments with this and found it wasn't true.

Really?

I was just in process of doing experiments using the attached test
program.  Here are some results (all using en_US/fr_FR or local
equivalent as the test locales, and all built with "gcc -O2").
The numbers are microseconds per loop iteration:
        -DUSE_LOC    no USE_LOC    no USE_LOC, locale1=C

HPUX 10.20        170        52.5        1.9
RHL 8 (glibc 2.2.93-5)    8.89        1.04        0.038
FC2 (glibc 2.3.3-27)    5.16        0.44        0.028
Darwin (OS X 10.3.5)    1199        1.75        0.35

(The third column is thrown in just to remind you of how awful standard
strcoll implementations are in themselves.)

These are on machines of widely varying horsepower, so the absolute
numbers shouldn't be compared across rows, but the general story holds:
setlocale should be considered to be at least an order of magnitude
slower than strcoll, and on non-glibc machines it can be a whole lot
worse than that.

> If the OS locale handling is slow on some OS's then postgres should just warn
> its users that using locales on those OS's will be slow.

I don't think we can take that attitude when the cost penalty involved
can be a couple of orders of magnitude.
        regards, tom lane


#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <locale.h>

int
main(int argc, char** argv)
{int        i, n, j = 0, k = 0;char   *locale1, *locale2;
locale1 = argv[1];locale2 = argv[2];n = atoi(argv[3]);
/* not ifdef'd, so that without USE_LOC we only test locale1 */if (setlocale(LC_COLLATE, locale1) == NULL){
printf("setlocale(LC_COLLATE,%s) failed\n", locale1);    exit(1);}if (setlocale(LC_CTYPE, locale1) == NULL){
printf("setlocale(LC_CTYPE,%s) failed\n", locale1);    exit(1);}
 
for (i = 0; i < n; i++){
#ifdef USE_LOC    if (setlocale(LC_COLLATE, locale1) == NULL)    {        printf("setlocale(LC_COLLATE, %s) failed\n",
locale1);       exit(1);    }    if (setlocale(LC_CTYPE, locale1) == NULL)    {        printf("setlocale(LC_CTYPE, %s)
failed\n",locale1);        exit(1);    }
 
#endif    j = strcoll("foobarbaz", "foo bar bath");
#ifdef USE_LOC    if (setlocale(LC_COLLATE, locale2) == NULL)    {        printf("setlocale(LC_COLLATE, %s) failed\n",
locale2);       exit(1);    }    if (setlocale(LC_CTYPE, locale2) == NULL)    {        printf("setlocale(LC_CTYPE, %s)
failed\n",locale2);        exit(1);    }
 
#endif    k = strcoll("foobarbaz", "foo bar bath");}
printf("in %s strcoll gives %d\n", locale1, j);
#ifdef USE_LOCprintf("in %s strcoll gives %d\n", locale2, k);
#endif
return 0;
}