Thread: Server locale?
Hi, Can you retrieve what the server is using for LOCALE settings? In particular, what the collate order is? It seems that my index is sorting case insensetively, and I can't see a reason why. Thanks in advance, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Martijn van Oosterhout <kleptog@svana.org> writes:
> Can you retrieve what the server is using for LOCALE settings? In
> particular, what the collate order is? It seems that my index is sorting
> case insensetively, and I can't see a reason why.
You can run contrib/pg_controldata (to become mainstream in 7.3 btw)
to see the server's LC_COLLATE and LC_CTYPE settings.
regards, tom lane
On Sat, Oct 05, 2002 at 08:39:39PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > Can you retrieve what the server is using for LOCALE settings? In
> > particular, what the collate order is? It seems that my index is sorting
> > case insensetively, and I can't see a reason why.
>
> You can run contrib/pg_controldata (to become mainstream in 7.3 btw)
> to see the server's LC_COLLATE and LC_CTYPE settings.
and by "SHOW ALL" or SHOW LC_ [ COLLATE | ... ] commands too.
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
On Mon, Oct 07, 2002 at 09:28:16AM +0200, Karel Zak wrote: > On Sat, Oct 05, 2002 at 08:39:39PM -0400, Tom Lane wrote: > > Martijn van Oosterhout <kleptog@svana.org> writes: > > > Can you retrieve what the server is using for LOCALE settings? In > > > particular, what the collate order is? It seems that my index is sorting > > > case insensetively, and I can't see a reason why. > > > > You can run contrib/pg_controldata (to become mainstream in 7.3 btw) > > to see the server's LC_COLLATE and LC_CTYPE settings. > > and by "SHOW ALL" or SHOW LC_ [ COLLATE | ... ] commands too. This must be a 7.3 thing. My 7.2.1 doesn't seem to support it. In any case, it seems that until recently there was no way of finding out, right? Short of accessing the backend directly. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Mon, Oct 07, 2002 at 10:08:47PM +1000, Martijn van Oosterhout wrote: > On Mon, Oct 07, 2002 at 09:28:16AM +0200, Karel Zak wrote: > > On Sat, Oct 05, 2002 at 08:39:39PM -0400, Tom Lane wrote: > > > Martijn van Oosterhout <kleptog@svana.org> writes: > > > > Can you retrieve what the server is using for LOCALE settings? In > > > > particular, what the collate order is? It seems that my index is sorting > > > > case insensetively, and I can't see a reason why. > > > > > > You can run contrib/pg_controldata (to become mainstream in 7.3 btw) > > > to see the server's LC_COLLATE and LC_CTYPE settings. > > > > and by "SHOW ALL" or SHOW LC_ [ COLLATE | ... ] commands too. > > This must be a 7.3 thing. My 7.2.1 doesn't seem to support it. In any case, > it seems that until recently there was no way of finding out, right? > Short of accessing the backend directly. Right, it's in 7.3. -- Karel Zak <zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
Karel Zak <zakkr@zf.jcu.cz> writes:
> On Mon, Oct 07, 2002 at 10:08:47PM +1000, Martijn van Oosterhout wrote:
>>> and by "SHOW ALL" or SHOW LC_ [ COLLATE | ... ] commands too.
>>
>> This must be a 7.3 thing. My 7.2.1 doesn't seem to support it. In any case,
>> it seems that until recently there was no way of finding out, right?
> Right, it's in 7.3.
7.3 has some of the LC_ things exposed as GUC variables, but not
COLLATE.
regression=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.3b2 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)
regression=# show lc_collate;
ERROR: Option 'lc_collate' is not recognized
regression=# show all;
...
lc_messages | C
lc_monetary | C
lc_numeric | C
lc_time | C
...
regards, tom lane
On Mon, Oct 07, 2002 at 10:17:35AM -0400, Tom Lane wrote:
> Karel Zak <zakkr@zf.jcu.cz> writes:
> > On Mon, Oct 07, 2002 at 10:08:47PM +1000, Martijn van Oosterhout wrote:
> >>> and by "SHOW ALL" or SHOW LC_ [ COLLATE | ... ] commands too.
> >>
> >> This must be a 7.3 thing. My 7.2.1 doesn't seem to support it. In any case,
> >> it seems that until recently there was no way of finding out, right?
>
> > Right, it's in 7.3.
>
> 7.3 has some of the LC_ things exposed as GUC variables, but not
> COLLATE.
Oh, sorry. By the way, why not COLLATE, why not all?
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
On Tue, Oct 08, 2002 at 09:07:49AM +0200, Karel Zak wrote: > On Mon, Oct 07, 2002 at 10:17:35AM -0400, Tom Lane wrote: > > 7.3 has some of the LC_ things exposed as GUC variables, but not > > COLLATE. > > Oh, sorry. By the way, why not COLLATE, why not all? That's really wierd. Anyway, it means that from a users point of view, ORDER BY on a string column has undefined results, since the server could be using any locale and you have no way of determining what it is. Best stick to sorting in the user code. Sigh. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Tue, Oct 08, 2002 at 05:25:16PM +1000, Martijn van Oosterhout wrote:
> On Tue, Oct 08, 2002 at 09:07:49AM +0200, Karel Zak wrote:
> > On Mon, Oct 07, 2002 at 10:17:35AM -0400, Tom Lane wrote:
> > > 7.3 has some of the LC_ things exposed as GUC variables, but not
> > > COLLATE.
> >
> > Oh, sorry. By the way, why not COLLATE, why not all?
>
> That's really wierd. Anyway, it means that from a users point of view, ORDER
> BY on a string column has undefined results, since the server could be using
> any locale and you have no way of determining what it is.
I good know why PostgreSQL not use all locale, I talked about SHOW
command. Why this command not show all LC_ values? The kernel of
PostgreSQL maybe not use all locale, but my function or some built-in
routines can use it (for example to_char()) and the SHOW command is
good way how check setting.
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
Karel Zak <zakkr@zf.jcu.cz> writes:
> I good know why PostgreSQL not use all locale, I talked about SHOW
> command. Why this command not show all LC_ values?
The GUC code doesn't currently have a concept of "a variable it's not
allowed to change, ever". If it did, we could expose initdb-time values
like LC_COLLATE that way. Might be a good thing to do, down the road
--- we could replace most of the uses of pg_controldata with read-only
SHOW variables.
regards, tom lane
Tom Lane wrote:
> Karel Zak <zakkr@zf.jcu.cz> writes:
> > I good know why PostgreSQL not use all locale, I talked about SHOW
> > command. Why this command not show all LC_ values?
>
> The GUC code doesn't currently have a concept of "a variable it's not
> allowed to change, ever". If it did, we could expose initdb-time values
> like LC_COLLATE that way. Might be a good thing to do, down the road
> --- we could replace most of the uses of pg_controldata with read-only
> SHOW variables.
Added to TODO:
o Allow SHOW of non-modifiable variables, like pg_controldata
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, Oct 08, 2002 at 10:02:43PM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > Karel Zak <zakkr@zf.jcu.cz> writes: > > > I good know why PostgreSQL not use all locale, I talked about SHOW > > > command. Why this command not show all LC_ values? > > > > The GUC code doesn't currently have a concept of "a variable it's not > > allowed to change, ever". If it did, we could expose initdb-time values > > like LC_COLLATE that way. Might be a good thing to do, down the road > > --- we could replace most of the uses of pg_controldata with read-only > > SHOW variables. > > Added to TODO: > > o Allow SHOW of non-modifiable variables, like pg_controldata Ok, I'm looking at guc.c right now and is there any reason why you couldn't just add a PGC_READONLY that throws an error when you try to change it. If this is the case I'd be willing to give it a shot. Or did you have something more substantial in mind? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Martijn van Oosterhout <kleptog@svana.org> writes:
>> Added to TODO:
>>
>> o Allow SHOW of non-modifiable variables, like pg_controldata
> Ok, I'm looking at guc.c right now and is there any reason why you couldn't
> just add a PGC_READONLY that throws an error when you try to change it. If
> this is the case I'd be willing to give it a shot.
I was thinking of adding a GUC_READONLY bit to the config struct's flags
word, instead. A PGC_ value that doesn't correspond to any possible
SetConfig context seems a little off key, somehow. But it might be
worth adding a PGC_INITDB value to GucContext also ... otherwise there's
no good value to put in the context field for these variables.
Possibly Peter will have a different thought about how it should be
done.
regards, tom lane