Thread: Setting locale per connection
Hi all,
I'm new to the list, so don't flame at the first date ;).
I usually use PostgreSQL for multiple languages, so I needed to
set locale per connection, or can change the locale on the fly.
I don't know if there is any such ability integrated in or not,
so I have wrote my 10lines function as a wrapper around
setlocale, that is attached. So what I do is just a simple
"SELECT locale('LC_COLLATE', 'fa_IR');" at connection time. Let
me know if there is any standard way already implemented.
Another silly question, isn't any way to get rid of seqscan, when
doing 'SELECT count(*) FROM tab;'?
Yours,
behdad
--
Behdad Esfahbod 11 Tir 1382, 2003 Jul 2
http://behdad.org/ [Finger for Geek Code]
If you do a job too well, you'll get stuck with it.
On Wed, Jul 02, 2003 at 07:22:51AM +0430, Behdad Esfahbod wrote: > Another silly question, isn't any way to get rid of seqscan, when > doing 'SELECT count(*) FROM tab;'? No :-( If you want to do that frequently, you should try to find another way to keep the count. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Crear es tan dificil como ser libre" (Elsa Triolet)
> I usually use PostgreSQL for multiple languages, so I needed to
> set locale per connection, or can change the locale on the fly.
> I don't know if there is any such ability integrated in or not,
> so I have wrote my 10lines function as a wrapper around
> setlocale, that is attached. So what I do is just a simple
> "SELECT locale('LC_COLLATE', 'fa_IR');" at connection time. Let
> me know if there is any standard way already implemented.
Don't know the answer to that one..
> Another silly question, isn't any way to get rid of seqscan, when
> doing 'SELECT count(*) FROM tab;'?
No, there's not. Due to PostgreSQL design restrictions. Just avoid doing
it, or use a trigger to keep a summary table or something.
Chris
On Wed, 2 Jul 2003, Behdad Esfahbod wrote:
> I'm new to the list, so don't flame at the first date ;).
>
> I usually use PostgreSQL for multiple languages, so I needed to
> set locale per connection, or can change the locale on the fly.
> I don't know if there is any such ability integrated in or not,
> so I have wrote my 10lines function as a wrapper around
> setlocale, that is attached. So what I do is just a simple
> "SELECT locale('LC_COLLATE', 'fa_IR');" at connection time. Let
> me know if there is any standard way already implemented.
Hmm, I'd think there'd be some potential for danger there. I don't play
with the locale stuff, but if the collation changes and you've got indexed
text (varchar, char) fields, wouldn't the index no longer necessarily be
in the correct order?
On Tue, 1 Jul 2003, Stephan Szabo wrote:
>
> On Wed, 2 Jul 2003, Behdad Esfahbod wrote:
>
> > I'm new to the list, so don't flame at the first date ;).
> >
> > I usually use PostgreSQL for multiple languages, so I needed to
> > set locale per connection, or can change the locale on the fly.
> > I don't know if there is any such ability integrated in or not,
> > so I have wrote my 10lines function as a wrapper around
> > setlocale, that is attached. So what I do is just a simple
> > "SELECT locale('LC_COLLATE', 'fa_IR');" at connection time. Let
> > me know if there is any standard way already implemented.
>
> Hmm, I'd think there'd be some potential for danger there. I don't play
> with the locale stuff, but if the collation changes and you've got indexed
> text (varchar, char) fields, wouldn't the index no longer necessarily be
> in the correct order?
I read in the FAQ that indexes for text fields is used just if
default C locale is used during initdb, well, humm, is not the
case on most distros. BTW, such a function is really needed to
make Unicode collation algorithms effective. I may be able to
convince my provider to define the function, but I can't convince
him to start the backend with my desired locale!
--
Behdad Esfahbod 11 Tir 1382, 2003 Jul 2
http://behdad.org/ [Finger for Geek Code]
If you do a job too well, you'll get stuck with it.
On Wed, 2 Jul 2003, Behdad Esfahbod wrote:
> On Tue, 1 Jul 2003, Stephan Szabo wrote:
>
> >
> > On Wed, 2 Jul 2003, Behdad Esfahbod wrote:
> >
> > > I'm new to the list, so don't flame at the first date ;).
> > >
> > > I usually use PostgreSQL for multiple languages, so I needed to
> > > set locale per connection, or can change the locale on the fly.
> > > I don't know if there is any such ability integrated in or not,
> > > so I have wrote my 10lines function as a wrapper around
> > > setlocale, that is attached. So what I do is just a simple
> > > "SELECT locale('LC_COLLATE', 'fa_IR');" at connection time. Let
> > > me know if there is any standard way already implemented.
> >
> > Hmm, I'd think there'd be some potential for danger there. I don't play
> > with the locale stuff, but if the collation changes and you've got indexed
> > text (varchar, char) fields, wouldn't the index no longer necessarily be
> > in the correct order?
>
> I read in the FAQ that indexes for text fields is used just if
> default C locale is used during initdb, well, humm, is not the
Indexes are only used for LIKE queries on the "C" locale, but they
should be used for standard =, <, >, etc queries in the other locales
so you may still run into trouble.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Wed, 2 Jul 2003, Behdad Esfahbod wrote:
>> so I have wrote my 10lines function as a wrapper around
>> setlocale, that is attached.
> Hmm, I'd think there'd be some potential for danger there. I don't play
> with the locale stuff, but if the collation changes and you've got indexed
> text (varchar, char) fields, wouldn't the index no longer necessarily be
> in the correct order?
Indeed, this is exactly why Postgres goes out of its way to prevent you
from changing the backend's collation setting on-the-fly. The proposed
function is a great way to shoot yourself in the foot :-(. If you doubt
it, check the archives from two or three years ago when we did not have
the interlock to force LC_COLLATE to be frozen at initdb time ...
regards, tom lane
On Wed, 2 Jul 2003, Tom Lane wrote: > > On Wed, 2 Jul 2003, Behdad Esfahbod wrote: > >> so I have wrote my 10lines function as a wrapper around > >> setlocale, that is attached. > > Indeed, this is exactly why Postgres goes out of its way to prevent you > from changing the backend's collation setting on-the-fly. The proposed > function is a great way to shoot yourself in the foot :-(. If you doubt > it, check the archives from two or three years ago when we did not have > the interlock to force LC_COLLATE to be frozen at initdb time ... > > regards, tom lane So, assuming I want to go for the right way, I've just seen the column-based LC_COLLATE in the TODO list. Is there any more information about that? behdad -- Behdad Esfahbod 11 Tir 1382, 2003 Jul 2 http://behdad.org/ [Finger for Geek Code] If you do a job too well, you'll get stuck with it.