Folks,

I've noticed in recent testing that current versions of both RedHat and SuSE
default to encodings of "en_US.UTF-8" in the US. Presumably they do
something corresponding in other countries.

I think this means that in the basic install-from-source instructions, we need
to warn people to compile with --locale='C'. I know it caught me by
surprise when indexing for LIKE stopped working, and I wasted a lot of time
hunting for a PG bug when it was my SuSE upgrade at fault.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From:	Peter Eisentraut
Subject:	Re: Doc patch needed: encodings?
Date:	Mon, 6 Dec 2004 02:00:51 +0100

Josh Berkus wrote:
> I've noticed in recent testing that current versions of both RedHat
> and SuSE default to encodings of "en_US.UTF-8" in the US.
> Presumably they do something corresponding in other countries.
>
> I think this means that in the basic install-from-source
> instructions, we need to warn people to compile with --locale='C'.

I think not.

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

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

From:	Josh Berkus
Subject:	Re: Doc patch needed: encodings?
Date:	Mon, 6 Dec 2004 09:23:02 -0800

Peter,

> > I think this means that in the basic install-from-source
> > instructions, we need to warn people to compile with --locale='C'.
>
> I think not.

Care to explain that? Why shouldn't we warn people?

Otherwise we're set for 12,000 e-mails to the SQL list on "why doesn't this
query use and index, it did on my old machine".

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

From:	Peter Eisentraut
Subject:	Re: Doc patch needed: encodings?
Date:	Mon, 6 Dec 2004 19:19:21 +0100

Josh Berkus wrote:
> Otherwise we're set for 12,000 e-mails to the SQL list on "why
> doesn't this query use and index, it did on my old machine".

Linux distributions have been shipping with non-C locale settings for a
long, long time, so that complaint would be invalid. And even back
then we figured that recommending that people turn locale off was not
an acceptable "solution".

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

From:	Josh Berkus
Subject:	Re: Doc patch needed: encodings?
Date:	Mon, 6 Dec 2004 10:39:17 -0800

Peter,

> Linux distributions have been shipping with non-C locale settings for a
> long, long time, so that complaint would be invalid.

In US distributions it's a recent thing. The switch to non-C locales is a
recent thing; RH Enterprise 3.0, and SuSE 9.0. But I expect in Europe you've
been using non-C locales for a while.

I'd like to have an explanation of this somewhere else newbies are liable to
read it, *before* their first production "LIKE" query doesn't use an index.
Where would be appropriate?

And, for English speakers, what exactly is wrong with using 'C' locale instead
of the environment one?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

From:	Peter Eisentraut
Subject:	Re: Doc patch needed: encodings?
Date:	Mon, 6 Dec 2004 20:02:43 +0100

Josh Berkus wrote:
> In US distributions it's a recent thing. The switch to non-C
> locales is a recent thing; RH Enterprise 3.0, and SuSE 9.0.

I have it on record that Red Hat has set a non-C locale by default at
least since Red Hat 6.1 as distributed in North America (aren't they
the same anyway?) in 1999. I know that because we had this exact
discussion back then.

> I'd like to have an explanation of this somewhere else newbies are
> liable to read it, *before* their first production "LIKE" query
> doesn't use an index. Where would be appropriate?

Near the documentation of "LIKE".

> And, for English speakers, what exactly is wrong with using 'C'
> locale instead of the environment one?

It makes it difficult to write a résumé, to name one thing.

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From:	Tom Lane
Subject:	Re: Doc patch needed: encodings?
Date:	Mon, 06 Dec 2004 14:13:28 -0500

Peter Eisentraut writes:
> Josh Berkus wrote:
>> I'd like to have an explanation of this somewhere else newbies are
>> liable to read it, *before* their first production "LIKE" query
>> doesn't use an index. Where would be appropriate?

> Near the documentation of "LIKE".

I think it would be fair to mention this somewhere near the discussion
of creating a database cluster, too. The existing documentation does
warn you that sort order may be affected by your choice, but there is
nothing anywhere near that section to suggest that LIKE performance
might be affected. A para in the "Locale Support" section (in
charset.sgml) would probably be appropriate, and maybe another word or
two in the place that link to it in runtime.sgml and ref/initdb.sgml.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From:	Bruce Momjian
Subject:	Re: Doc patch needed: encodings?
Date:	Mon, 3 Jan 2005 19:05:38 -0500 (EST)

--ELM1104797138-1052-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

I have applied the following patch to mention non-C locales affect LIKE.

---------------------------------------------------------------------------

Tom Lane wrote:
> Peter Eisentraut writes:
> > Josh Berkus wrote:
> >> I'd like to have an explanation of this somewhere else newbies are
> >> liable to read it, *before* their first production "LIKE" query
> >> doesn't use an index. Where would be appropriate?
>
> > Near the documentation of "LIKE".
>
> I think it would be fair to mention this somewhere near the discussion
> of creating a database cluster, too. The existing documentation does
> warn you that sort order may be affected by your choice, but there is
> nothing anywhere near that section to suggest that LIKE performance
> might be affected. A para in the "Locale Support" section (in
> charset.sgml) would probably be appropriate, and maybe another word or
> two in the place that link to it in runtime.sgml and ref/initdb.sgml.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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

--ELM1104797138-1052-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename="/bjm/diff"

Index: doc/src/sgml/charset.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/charset.sgml,v
retrieving revision 2.47
diff -c -c -r2.47 charset.sgml
*** doc/src/sgml/charset.sgml 27 Dec 2004 22:30:10 -0000 2.47
--- doc/src/sgml/charset.sgml 4 Jan 2005 00:02:40 -0000
***************
*** 189,198 ****

! Benefits</> <para> ! Locale support influences in particular the following features: <itemizedlist> <listitem> --- 189,198 ---- </sect2> <sect2> ! <title>Behavior</> <para> ! Locale support influences the following features: <itemizedlist> <listitem> *************** *** 204,209 **** --- 204,216 ---- <listitem> <para> + The ability to use indexes with <literal>LIKE</> clauses + <indexterm><primary>LIKE</><secondary>and locales</></indexterm> + </para> + </listitem> + + <listitem> + <para> The <function>to_char</> family of functions </para> </listitem> *************** *** 211,219 **** </para> <para> ! The only severe drawback of using the locale support in ! <productname>PostgreSQL</> is its speed. So use locales only if ! you actually need them. </para> </sect2> --- 218,228 ---- </para> <para> ! The drawback of using locales other than <literal>C</> or ! <literal>POSIX</> in <productname>PostgreSQL</> is its performance ! impact. It slows character handling and prevents ordinary indexes ! from being used by <literal>LIKE</>. For this reason use locales ! only if you actually need them. </para> </sect2> Index: doc/src/sgml/runtime.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v retrieving revision 1.299 diff -c -c -r1.299 runtime.sgml *** doc/src/sgml/runtime.sgml 26 Dec 2004 23:06:56 -0000 1.299 --- doc/src/sgml/runtime.sgml 4 Jan 2005 00:02:56 -0000 *************** *** 144,152 **** that can be found in <xref linkend="locale">. The sort order used within a particular database cluster is set by <command>initdb</command> and cannot be changed later, short of ! dumping all data, rerunning <command>initdb</command>, and ! reloading the data. So it's important to make this choice correctly ! the first time. </para> </sect1> --- 144,153 ---- that can be found in <xref linkend="locale">. The sort order used within a particular database cluster is set by <command>initdb</command> and cannot be changed later, short of ! dumping all data, rerunning <command>initdb</command>, and reloading ! the data. There is also a performance impact for using locales ! other than <literal>C</> or <literal>POSIX</>. Therefore, it is ! important to make this choice correctly the first time. </para> </sect1> Index: doc/src/sgml/ref/initdb.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/initdb.sgml,v retrieving revision 1.32 diff -c -c -r1.32 initdb.sgml *** doc/src/sgml/ref/initdb.sgml 1 Aug 2004 06:19:18 -0000 1.32 --- doc/src/sgml/ref/initdb.sgml 4 Jan 2005 00:02:57 -0000 *************** *** 54,74 **** </para> <para> ! <command>initdb</command> initializes the database cluster's ! default locale and character set encoding. Some locale categories ! are fixed for the lifetime of the cluster, so it is important to ! make the right choice when running <command>initdb</command>. ! Other locale categories can be changed later when the server is ! started. <command>initdb</command> will write those locale ! settings into the <filename>postgresql.conf</filename> ! configuration file so they are the default, but they can be changed ! by editing that file. To set the locale that ! <command>initdb</command> uses, see the description of the ! <option>--locale</option> option. The character set encoding can be set separately for each database as it is created. <command>initdb</command> determines the encoding for the <literal>template1</literal> database, which will serve as the ! default for all other databases. To alter the default encoding use the <option>--encoding</option> option. </para> --- 54,75 ---- </para> <para> ! <command>initdb</command> initializes the database cluster's default ! locale and character set encoding. Some locale categories are fixed ! for the lifetime of the cluster. There is also a performance impact ! in using locales other than <literal>C</> or <literal>POSIX</>. ! Therefore it is important to make the right choice when running ! <command>initdb</command>. Other locale categories can be changed ! later when the server is started. <command>initdb</command> will ! write those locale settings into the ! <filename>postgresql.conf</filename> configuration file so they are ! the default, but they can be changed by editing that file. To set the ! locale that <command>initdb</command> uses, see the description of ! the <option>--locale</option> option. The character set encoding can be set separately for each database as it is created. <command>initdb</command> determines the encoding for the <literal>template1</literal> database, which will serve as the ! default for all other databases. To alter the default encoding use the <option>--encoding</option> option. </para> --ELM1104797138-1052-0_ Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org --ELM1104797138-1052-0_-- </td></tr></table></td></tr><tr><td> <table cellpadding=0 cellspacing=0 class=txt width=100%><tr><td><img src=/0.gif width=320 height=1></td><td bgcolor=#e0e0e0 width=100%><table class=txt><tr><td><a name=a8>From:</a></td><td>Peter Eisentraut</td></tr><tr><td>Subject:</td><td>Re: Doc patch needed: encodings?</td></tr><tr><td>Date:</td><td>Tue, 4 Jan 2005 01:12:31 +0100</td></tr></table></tr></table></td></tr><tr><td> <table cellpadding=0 cellspacing=0 class=txt><tr><td><img src=http:/0.gif width=320 height=1></td><td>Bruce Momjian wrote: > I have applied the following patch to mention non-C locales affect > LIKE. I don't think it's accurate to say that there is a performance impact. The only impact is that you need to create a different kind of index. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) </td></tr></table></td></tr><tr><td> <table cellpadding=0 cellspacing=0 class=txt width=100%><tr><td><img src=/0.gif width=360 height=1></td><td bgcolor=#e0e0e0 width=100%><table class=txt><tr><td><a name=a9>From:</a></td><td>Bruce Momjian</td></tr><tr><td>Subject:</td><td>Re: Doc patch needed: encodings?</td></tr><tr><td>Date:</td><td>Mon, 3 Jan 2005 19:16:39 -0500 (EST)</td></tr></table></tr></table></td></tr><tr><td> <table cellpadding=0 cellspacing=0 class=txt><tr><td><img src=http:/0.gif width=360 height=1></td><td>Peter Eisentraut wrote: > Bruce Momjian wrote: > > I have applied the following patch to mention non-C locales affect > > LIKE. > > I don't think it's accurate to say that there is a performance impact. > The only impact is that you need to create a different kind of index. Well, there is a performance impact to using the locale functions, and you can't use ordinary indexes for LIKE, just special ones. Is there better text I should use? I used "performance impact" in the reference text to suggest there is an issue and they might want to read the locale section. -- 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly </td></tr></table></td></tr><tr><td> <table cellpadding=0 cellspacing=0 class=txt width=100%><tr><td><img src=/0.gif width=200 height=1></td><td bgcolor=#e0e0e0 width=100%><table class=txt><tr><td><a name=a10>From:</a></td><td>Robert Treat</td></tr><tr><td>Subject:</td><td>Re: Doc patch needed: encodings?</td></tr><tr><td>Date:</td><td>Mon, 6 Dec 2004 13:48:53 -0500</td></tr></table></tr></table></td></tr><tr><td> <table cellpadding=0 cellspacing=0 class=txt><tr><td><img src=http:/0.gif width=200 height=1></td><td>On Monday 06 December 2004 13:39, Josh Berkus wrote: > I'd like to have an explanation of this somewhere else newbies are liable > to read it, *before* their first production "LIKE" query doesn't use an > index. Where would be appropriate? > It's buried in the faq -> http://www.postgresql.org/docs/faqs/FAQ.html#4.8 -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings </td></tr></table></td></tr></table></td></tr></table></td><td> </td><td valign=top align=right> </td></tr></table> </td> <td><img src=0.gif width=1 height=300></td> </tr> </table> <table cellpadding=0 cellspacing=0><tr><td><img src=/0.gif height=3></td></tr></table> <table cellpadding=0 cellspacing=0 border=0 bgcolor=#a0a000 width=100%><tr><td><img src=/0.gif></td></tr></table> <table class=stxt width=100% border=0 bgcolor=#f4f5c2 cellspacing=0> <tr><td> Copyright © 2006 knowledge-database - All rights reserved </td></tr> </table> <table cellpadding=0 cellspacing=0 border=0 bgcolor=#a0a000 width=100%><tr><td><img src=/0.gif></td></tr></table> </body> </html>