Обсуждение: How to get fast ~ operator using C locale

Hello, I want to have pg use fast indexing for ~ operator. Is setting
C locale for this is still necessary? If yes, is it enough to
initdb/createdb with C locale? If it is not enough, why setting locale
to C and starting postmaster still gives other locale, for example,
pl_PL, in
postmaster log "initial environ dump"?

Best regards,
Artur

Hi,

Le jeudi 05 juillet 2007, Artur Rataj a écrit :
> Hello, I want to have pg use fast indexing for ~ operator. Is setting
> C locale for this is still necessary? If yes, is it enough to
> initdb/createdb with C locale? If it is not enough, why setting locale
> to C and starting postmaster still gives other locale, for example,
> pl_PL, in
> postmaster log "initial environ dump"?

It seems to me this page of the fine manual could match:

http://www.postgresql.org/docs/8.2/static/indexes-opclass.html

The operator classes text_pattern_ops, varchar_pattern_ops,
bpchar_pattern_ops, and name_pattern_ops support B-tree indexes on the types
text, varchar, char, and name, respectively. The difference from the default
operator classes is that the values are compared strictly character by
character rather than according to the locale-specific collation rules. This
makes these operator classes suitable for use by queries involving pattern
matching expressions (LIKE or POSIX regular expressions) when the server does
not use the standard "C" locale. As an example, you might index a varchar
column like this:
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

Regards,
--
dim

But I want the server to use the C locale. I set the locale to C and
run postmaster. I supposed it uses C locale, but -d 5 shows in log:

DEBUG:  /usr/bin/postmaster: PostmasterMain: initial environ dump:
.
.
.

DEBUG:          LC_COLLATE=pl_PL.UTF-8
DEBUG:          LC_CTYPE=pl_PL.UTF-8
DEBUG:          LC_MESSAGES=pl_PL.UTF-8
DEBUG:          LC_MONETARY=C
DEBUG:          LC_NUMERIC=C
DEBUG:          LC_TIME=C

So, does it mean that the server does not really use C locale?


On 7/5/07, Dimitri Fontaine <dfontaine@hi-media.com> wrote:
> Hi,
>
> Le jeudi 05 juillet 2007, Artur Rataj a écrit:
> > Hello, I want to have pg use fast indexing for ~ operator. Is setting
> > C locale for this is still necessary? If yes, is it enough to
> > initdb/createdb with C locale? If it is not enough, why setting locale
> > to C and starting postmaster still gives other locale, for example,
> > pl_PL, in
> > postmaster log "initial environ dump"?
>
> It seems to me this page of the fine manual could match:
>
> http://www.postgresql.org/docs/8.2/static/indexes-opclass.html
>
> The operator classes text_pattern_ops, varchar_pattern_ops,
> bpchar_pattern_ops, and name_pattern_ops support B-tree indexes on the types
> text, varchar, char, and name, respectively. The difference from the default
> operator classes is that the values are compared strictly character by
> character rather than according to the locale-specific collation rules. This
> makes these operator classes suitable for use by queries involving pattern
> matching expressions (LIKE or POSIX regular expressions) when the server does
> not use the standard "C" locale. As an example, you might index a varchar
> column like this:
> CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
>
> Regards,
> --
> dim
>
>

On 7/5/07, Artur Rataj <arturrataj@gmail.com> wrote:
> But I want the server to use the C locale. I set the locale to C and
> run postmaster. I supposed it uses C locale, but -d 5 shows in log:

The locale for indexing is set during initdb:
initdb --no-locale

The ~ operator does consider using btree regardless of locale unless
your right hand term is a constant and you can reduce to a
(functional) boolean index...so ISTM you are looking in the wrong
place anyways...try tsearch2?

merlin

On Thu, Jul 05, 2007 at 02:11:24PM +0200, Artur Rataj wrote:
> But I want the server to use the C locale. I set the locale to C and
> run postmaster. I supposed it uses C locale, but -d 5 shows in log:

The locale is decided when you run initdb. Once the cluster has been
created you can't change it.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.