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)

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 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 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.