Thread

Setting locale per connection

Behdad Esfahbod <behdad@bamdad.org> — 2003-07-02T02:52:51Z

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.

Re: Setting locale per connection

Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-07-02T02:56:59Z

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)

Re: Setting locale per connection

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-07-02T03:05:18Z

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?

Re: Setting locale per connection

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2003-07-02T03:12:42Z

> 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

Re: Setting locale per connection

Behdad Esfahbod <behdad@bamdad.org> — 2003-07-02T03:23:46Z

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.

Re: Setting locale per connection

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-07-02T04:12:26Z

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.

Re: Setting locale per connection

Tom Lane <tgl@sss.pgh.pa.us> — 2003-07-02T05:58:57Z

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

Re: Setting locale per connection

Behdad Esfahbod <behdad@bamdad.org> — 2003-07-02T10:03:54Z

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.