Re: [HACKERS] Setting locale per connection
Behdad Esfahbod <[EMAIL PROTECTED]> writes: > 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? If you troll the pghackers archives you will find several past discussions about it. A reasonably efficient implementation would likely require implementing our own locale support :-(, which is a large enough task that no one's tried it yet... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Setting locale per connection
Stephan Szabo <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 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.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 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?
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] 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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 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 () "Crear es tan dificil como ser libre" (Elsa Triolet) ---(end of broadcast)--- TIP 8: explain analyze is your friend
