initdb in 8.3

Tim Tassonis wrote:

Hi

I just recently compiled and installed 8.3.1 on a System that has UTF-8
as the default characterset in the environment. Copied the binaries, run
initdb without parameters, the usual stuff.

As you probably are all aware of, this results now in a cluster that
will only allow you to create UTF-8 databases. I have read some posts
regarding this topic where it is explained that allowing LATIN1 on a
cluster initialized with UTF-8 will give you problems anyway etc and you
have to use locale=C to be able to create databases with different
charactersets.

You can only have one locale per installation (initdb'd cluster).
You can create a database with any encoding that is compatible with that
locale (LATIN1, LATIN9, UTF-8, etc).

Now, the locale controls (amongst other things) all your sorting. If you
choose a locale of "C" you get a simple binary sorting. By default PG
tries to match whatever sorting you have set up on your operating-system.

See the difference below:

richardh@server3:db$ LANG=C sort /tmp/words.txt
apple
berry
Apple
apple
apples
the apple

richardh@server3:db$ LANG=en_GB.UTF-8 sort /tmp/words.txt
apple
apple
Apple
apples
berry
the apple

I think someone is looking at per-database locales for 8.4 - the issue
is more tricky than you might think because you need to worry about
system catalogue sort-order.

--
Richard Huxton
Archonet Ltd

Am Mittwoch, 23. April 2008 schrieb Tim Tassonis:

My question is: Why then is --locale=C not the default for initdb, as I
do regard it as a rather big annoyance that a default installation on
probably almost any modern linux distribution results in a UTF-8 only
cluster, fixable only by dropping all databases, rerun initdb and the
reimporting them again.

Because the vast majority of users prefers UTF-8 encoded databases over C
locale databases.

Peter Eisentraut wrote:

Am Mittwoch, 23. April 2008 schrieb Tim Tassonis:

My question is: Why then is --locale=C not the default for initdb, as I
do regard it as a rather big annoyance that a default installation on
probably almost any modern linux distribution results in a UTF-8 only
cluster, fixable only by dropping all databases, rerun initdb and the
reimporting them again.

Because the vast majority of users prefers UTF-8 encoded databases over C
locale databases.

Ok, let me put it in another way. If UTF-8 is chosen at initdb, only
UTF-8 databases can be created, if C is chosen, you can specify
different encodings (UTF-8, LATIN1 etc) for each database.

As I understood now, sorting will then still be in C style and not in
the locale specific way. Which leads me to the following questions:

If specifying a characterset different from the default locale for a
database is such a bad idea, why is it possible at all?

From how I understand you, if I wanted a postgres server machine
supporting databases with different charsets, I'm advised to initialise
one cluster per locale.

If specifying a characterset different from the default locale for a
database is not a bad idea, why does the default install forbid me to do
exactly this?

Regards
Tim

On Wed, Apr 23, 2008 at 04:35:04PM +0200, Tim Tassonis wrote:

Ok, let me put it in another way. If UTF-8 is chosen at initdb, only
UTF-8 databases can be created, if C is chosen, you can specify
different encodings (UTF-8, LATIN1 etc) for each database.

As I understood now, sorting will then still be in C style and not in
the locale specific way. Which leads me to the following questions:

If specifying a characterset different from the default locale for a
database is such a bad idea, why is it possible at all?

It isn't possible, that's the point. What is possible is that client
can use any encoding they like to talk to the server, but the server
will store and manage it all in one. What locale C means "I'm an
encoding wizard and will ensure all my programs can handle all the
encodings I want to use, because I understand the database will treat
everything I send as ASCII bytes no matter what encoding the clients
say it is".

From how I understand you, if I wanted a postgres server machine
supporting databases with different charsets, I'm advised to initialise
one cluster per locale.

If you want to control the *storage* charset, yes. If you just want
clients to think it's a LATIN9 DB, doing a:

ALTER DATABASE foo SET client_encoding=latin9;

If specifying a characterset different from the default locale for a
database is not a bad idea, why does the default install forbid me to do
exactly this?

It is a bad idea, because most normal the C library can only handle one
encoding at a time. Locale C is a backdoor because it has system
independant semantics and does not require libc. It's also not what
people usually want, and so not recommended.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Am Mittwoch, 23. April 2008 schrieb Tim Tassonis:

If specifying a characterset different from the default locale for a
database is such a bad idea, why is it possible at all?

Because Japanese users need this functionality. Aside from spectacularly
bizarre niche applications, that is really the only reason. If you are
dealing with European-type languages, just forget about it and specify the
locale you want to initdb and let the encodings fall into place.

Martijn van Oosterhout wrote:

On Wed, Apr 23, 2008 at 04:35:04PM +0200, Tim Tassonis wrote:

If specifying a characterset different from the default locale for a
database is such a bad idea, why is it possible at all?

It isn't possible, that's the point. What is possible is that client
can use any encoding they like to talk to the server, but the server
will store and manage it all in one. What locale C means "I'm an
encoding wizard and will ensure all my programs can handle all the
encodings I want to use, because I understand the database will treat
everything I send as ASCII bytes no matter what encoding the clients
say it is".

From how I understand you, if I wanted a postgres server machine
supporting databases with different charsets, I'm advised to initialise
one cluster per locale.

If you want to control the *storage* charset, yes. If you just want
clients to think it's a LATIN9 DB, doing a:

ALTER DATABASE foo SET client_encoding=latin9;

Ok, got it, it's really this setting that's interesting. If I have a
legacy application that defaults to latin1, I can leave the DB to UTF-8
,set the client_encoding to latin1 and then all my selects and inserts
can use latin1, but the data will be stored in utf-8.

Well, that's really all I need, sorry for the confusion.

Thanks a lot
Tim

I have a question related to this issue:
Now that the locale has changed, it seems that the planner no longer
wants to use the indexes for running LIKE queries on varchar columns
unless I specify varchar_pattern_ops when creating the index. And if I
create the index with varchar_pattern_ops, then the planner won't use
it for = queries.

What's the correct solution to this problem (when using UTF-8 and
lc_collate and lc_ctype are both 1252)? Do I need to create two indexes?

Thanks,
-Chris

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tim Tassonis
Sent: Wednesday, April 23, 2008 12:22
To: Martijn van Oosterhout
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] initdb in 8.3

Martijn van Oosterhout wrote:

On Wed, Apr 23, 2008 at 04:35:04PM +0200, Tim Tassonis wrote:

If specifying a characterset different from the default locale for a
database is such a bad idea, why is it possible at all?

It isn't possible, that's the point. What is possible is that client
can use any encoding they like to talk to the server, but the server
will store and manage it all in one. What locale C means "I'm an
encoding wizard and will ensure all my programs can handle all the
encodings I want to use, because I understand the database will treat
everything I send as ASCII bytes no matter what encoding the clients
say it is".

From how I understand you, if I wanted a postgres server machine
supporting databases with different charsets, I'm advised to

initialise

one cluster per locale.

If you want to control the *storage* charset, yes. If you just want
clients to think it's a LATIN9 DB, doing a:

ALTER DATABASE foo SET client_encoding=latin9;

Ok, got it, it's really this setting that's interesting. If I have a
legacy application that defaults to latin1, I can leave the DB to UTF-8
,set the client_encoding to latin1 and then all my selects and inserts
can use latin1, but the data will be stored in utf-8.

Well, that's really all I need, sorry for the confusion.

Thanks a lot
Tim

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Apr 23, 2008 at 11:46:35AM +0200, Tim Tassonis wrote:

As you probably are all aware of, this results now in a cluster that
will only allow you to create UTF-8 databases. I have read some posts
regarding this topic where it is explained that allowing LATIN1 on a
cluster initialized with UTF-8 will give you problems

Ain't it the other way round ?

Creating UTF8 databases on LATIN1 clusters won't work ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Karsten Hilbert wrote:

On Wed, Apr 23, 2008 at 11:46:35AM +0200, Tim Tassonis wrote:

As you probably are all aware of, this results now in a cluster that
will only allow you to create UTF-8 databases. I have read some posts
regarding this topic where it is explained that allowing LATIN1 on a
cluster initialized with UTF-8 will give you problems

Ain't it the other way round ?

Creating UTF8 databases on LATIN1 clusters won't work ?

No it's this way round, but maybe the other way round, too.
You can only create databases with arbitrary encodings on C locale
clusters from 8.3 on.

Regards
Tim

Christopher Condit wrote:

I have a question related to this issue:
Now that the locale has changed, it seems that the planner no longer
wants to use the indexes for running LIKE queries on varchar columns
unless I specify varchar_pattern_ops when creating the index. And if I
create the index with varchar_pattern_ops, then the planner won't use
it for = queries.

What's the correct solution to this problem (when using UTF-8 and
lc_collate and lc_ctype are both 1252)? Do I need to create two indexes?

http://www.postgresql.org/docs/faqs.FAQ.html#item4.6
http://www.postgresql.org/docs/8.3/static/indexes-opclass.html

It should really also be mentioned in the section on the LIKE operator.

By the way, when I tried to leave a comment on the pattern matching
operator doc page I found that after logging in I was presented with a
blank comments form again. When I re-entered my comment and submitted I
got an error indicating that '' is not a valid NUMERIC. Going back to
the original comments form on the pattern matching operator doc page
(having logged in) and submitting there works fine.

--
Craig Ringer

Ahhh - I See. Thanks, Craig.
Although, once you've built the index with varchar_pattern_ops index,
the following two (essentially equivalent) queries will run at vastly
different speeds:
select * from A where A.value like 'Nacho';
select * from A where A.value = 'Nacho';

Seems that the optimizer should catch this...
C

-----Original Message-----
From: Craig Ringer [mailto:craig@postnewspapers.com.au]
Sent: Thursday, April 24, 2008 01:20
To: Christopher Condit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] initdb in 8.3

Christopher Condit wrote:

I have a question related to this issue:
Now that the locale has changed, it seems that the planner no longer
wants to use the indexes for running LIKE queries on varchar columns
unless I specify varchar_pattern_ops when creating the index. And if I
create the index with varchar_pattern_ops, then the planner won't use
it for = queries.

What's the correct solution to this problem (when using UTF-8 and
lc_collate and lc_ctype are both 1252)? Do I need to create two

indexes?

http://www.postgresql.org/docs/faqs.FAQ.html#item4.6
http://www.postgresql.org/docs/8.3/static/indexes-opclass.html

It should really also be mentioned in the section on the LIKE operator.

By the way, when I tried to leave a comment on the pattern matching
operator doc page I found that after logging in I was presented with a
blank comments form again. When I re-entered my comment and submitted I
got an error indicating that '' is not a valid NUMERIC. Going back to
the original comments form on the pattern matching operator doc page
(having logged in) and submitting there works fine.

--
Craig Ringer

"Christopher Condit" <condit@sdsc.edu> writes:

Although, once you've built the index with varchar_pattern_ops index,
the following two (essentially equivalent) queries will run at vastly
different speeds:
select * from A where A.value like 'Nacho';
select * from A where A.value = 'Nacho';

Seems that the optimizer should catch this...

Yeah, it's on the to-do list ...
http://archives.postgresql.org/pgsql-hackers/2008-02/msg01003.php

regards, tom lane

Richard Huxton napsal(a):

I think someone is looking at per-database locales for 8.4 - the issue
is more tricky than you might think because you need to worry about
system catalogue sort-order.

There is Google Soc project for implementing collation per database level. I
hope it will appear in 8.4 version.

Catalog sort-order is not affected by locale, because "name" data type uses
different operator then varchar.

Zdenek