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
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
[HACKERS] 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.
#include pgsql/postgres.h
#include locale.h
bool
pgbe_setlocale (void *cat, void *loc)
{
int category;
void *catstr;
if (!loc)
return false;
if (cat)
catstr = VARDATA (cat);
else
catstr = LC_ALL; /* default to LC_ALL */
#define CHECKCATEGORY(s, i) if (!strcmp (s, catstr)) category = i;
/* *INDENT-OFF* */
CHECKCATEGORY (LC_ALL, LC_ALL) else
CHECKCATEGORY (LC_COLLATE, LC_COLLATE) else
CHECKCATEGORY (LC_CTYPE,LC_CTYPE) else
CHECKCATEGORY (LC_MESSAGES, LC_MESSAGES)else
CHECKCATEGORY (LC_MONETARY, LC_MONETARY)else
CHECKCATEGORY (LC_NUMERIC, LC_NUMERIC) else
CHECKCATEGORY (LC_TIME, LC_TIME)else
return false;
/* *INDENT-ON* */
#undef CHECKCATEGORY
return setlocale (category, VARDATA (loc)) ? true : false;
}
---(end of broadcast)---
TIP 8: explain analyze is your friend
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 (alvherre[a]dcc.uchile.cl) Crear es tan dificil como ser libre (Elsa Triolet) ---(end of broadcast)--- TIP 8: explain analyze is your friend
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, 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
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:
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
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
