pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help understanding locale
4+ messages / 2 participants
[nested] [flat]

* Help understanding locale
@ 2004-09-01 18:40 Chris Hoover <revoohc@sermonaudio.com>
  2004-09-01 20:39 ` Re: Help understanding locale Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 4+ messages in thread

From: Chris Hoover @ 2004-09-01 18:40 UTC (permalink / raw)
  To: pgsql-admin

I need some help understanding locales and what the impact upon my 
database/application would be.

I am currently running 7.3.4 and have a database with tons of varchar 
fields.  However, when we query on one of the fields with a like, it 
does not use an index.  Apparently from the documentation, this is due 
to the locale not being set to C.  The locale command gives me the 
following on my server:
LANG=en_US
LC_CTYPE="en_US"
LC_NUMERIC="en_US"
LC_TIME="en_US"
LC_COLLATE="en_US"
LC_MONETARY="en_US"
LC_MESSAGES="en_US"
LC_PAPER="en_US"
LC_NAME="en_US"
LC_ADDRESS="en_US"
LC_TELEPHONE="en_US"
LC_MEASUREMENT="en_US"
LC_IDENTIFICATION="en_US"
LC_ALL=


I am just starting a project to do an upgrade to our application and 
looking at trying to fix this problem since it is affecting our 
performance.  I have upgraded our dbserver to 7.3.7 (our next project is 
a major db upgrade to 7.4 or even 8), and created a new cluster with the 
locale specifically set to C (initdb --locale=C ...).  This has fixed 
the index usage problem, but I don't understand what ramifications if 
any it has on my database.  Please help me understand what this means to 
me as the dba. 

Thanks,

Chris




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Help understanding locale
  2004-09-01 18:40 Help understanding locale Chris Hoover <revoohc@sermonaudio.com>
@ 2004-09-01 20:39 ` Peter Eisentraut <peter_e@gmx.net>
  2004-09-01 21:03   ` Re: Help understanding locale Chris Hoover <revoohc@sermonaudio.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Peter Eisentraut @ 2004-09-01 20:39 UTC (permalink / raw)
  To: Chris Hoover <revoohc@sermonaudio.com>; pgsql-admin

Chris Hoover wrote:
> with the locale specifically set to C (initdb --locale=C ...).  This
> has fixed the index usage problem, but I don't understand what
> ramifications if any it has on my database.  Please help me
> understand what this means to me as the dba.

The sort order will be different.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Help understanding locale
  2004-09-01 18:40 Help understanding locale Chris Hoover <revoohc@sermonaudio.com>
  2004-09-01 20:39 ` Re: Help understanding locale Peter Eisentraut <peter_e@gmx.net>
@ 2004-09-01 21:03   ` Chris Hoover <revoohc@sermonaudio.com>
  2004-09-02 07:56     ` Re: Help understanding locale Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 4+ messages in thread

From: Chris Hoover @ 2004-09-01 21:03 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-admin

Would you please be so kind to provide a bit more explanation?  How will 
the sort differ?  Are the any good pages out there that would help me 
understand the ramifications?

Thanks,

Chris
Peter Eisentraut wrote:

>Chris Hoover wrote:
>  
>
>>with the locale specifically set to C (initdb --locale=C ...).  This
>>has fixed the index usage problem, but I don't understand what
>>ramifications if any it has on my database.  Please help me
>>understand what this means to me as the dba.
>>    
>>
>
>The sort order will be different.
>
>  
>





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Help understanding locale
  2004-09-01 18:40 Help understanding locale Chris Hoover <revoohc@sermonaudio.com>
  2004-09-01 20:39 ` Re: Help understanding locale Peter Eisentraut <peter_e@gmx.net>
  2004-09-01 21:03   ` Re: Help understanding locale Chris Hoover <revoohc@sermonaudio.com>
@ 2004-09-02 07:56     ` Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 4+ messages in thread

From: Peter Eisentraut @ 2004-09-02 07:56 UTC (permalink / raw)
  To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: pgsql-admin

Am Mittwoch, 1. September 2004 23:03 schrieb Chris Hoover:
> Would you please be so kind to provide a bit more explanation?  How will
> the sort differ?

C sorts by byte value, which ends up being AB...Zab...z.  en_US sorts using 
"dictionary order", which means aAbB...zZ, accented characters appear at 
their correct place, whitespace is ignored.  The latter in principle applies 
to all other language-specific locales as well, except that the particular 
relative order of the letters may be different.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-09-02 07:56 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-01 18:40 Help understanding locale Chris Hoover <revoohc@sermonaudio.com>
2004-09-01 20:39 ` Peter Eisentraut <peter_e@gmx.net>
2004-09-01 21:03   ` Chris Hoover <revoohc@sermonaudio.com>
2004-09-02 07:56     ` Peter Eisentraut <peter_e@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox