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.
Wed, 2 Jul 2003 07:22:51 +0430 (IRST)
Behdad Esfahbod <behdad@bamdad.org>
Setting locale per connection
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)

Tue, 1 Jul 2003 22:56:59 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Setting locale per connection

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?



Tue, 1 Jul 2003 20:05:18 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Setting locale per connection
> 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


Wed, 2 Jul 2003 11:12:42 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Setting locale per connection
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.


Wed, 2 Jul 2003 07:53:46 +0430 (IRST)
Behdad Esfahbod <behdad@bamdad.org>
Re: Setting locale per connection
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.


Tue, 1 Jul 2003 21:12:26 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Setting locale per connection
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

Wed, 02 Jul 2003 01:58:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Setting locale per connection 
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.


Wed, 2 Jul 2003 14:33:54 +0430 (IRST)
Behdad Esfahbod <behdad@bamdad.org>
Re: Setting locale per connection 