Re: How to find correct locale name for CREATE DATABASE

I tried in Debian ("PostgreSQL 9.0.1 on x86_64-pc-linux-gnu, compiled by
GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit") :

1. CREATE DATABASE "mydb" WITH TEMPLATE=template0
LC_COLLATE='Estonian_Estonia.1257' LC_CTYPE='Estonian_Estonia.1257'
OWNER="mydb_owner" ENCODING='UNICODE'

2. CREATE DATABASE "mydb" WITH TEMPLATE=template0 LC_COLLATE='et_EE.UTF-8'
LC_CTYPE='et_EE.UTF-8' OWNER="mydb_owner" ENCODING='UNICODE'

3. 2. CREATE DATABASE "mydb" WITH TEMPLATE=template0
LC_COLLATE='et_EE.UTF8'
LC_CTYPE='et_EE.UTF8' OWNER="mydb_owner" ENCODING='UNICODE'

in all cases same error

invalid locale name

occurs.

Questions:

1. How to find correct locale name for Estonian in Debian Linux ?
In Windows case (1) works.

2. How to create portable CREATE DATABASE command for estonian locale which
works in all operating systems?

3. Or how to detect OS from PostgreSql server and select correct Estonian
locale ?

Andrus.

2010/11/30 Andrus <kobruleht2(at)hot(dot)ee>:
> I tried in  Debian ("PostgreSQL 9.0.1 on x86_64-pc-linux-gnu, compiled by
> GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit") :
>
> 1. CREATE DATABASE "mydb" WITH TEMPLATE=template0
> LC_COLLATE='Estonian_Estonia.1257' LC_CTYPE='Estonian_Estonia.1257'
> OWNER="mydb_owner" ENCODING='UNICODE'
>
> 2. CREATE DATABASE "mydb" WITH TEMPLATE=template0  LC_COLLATE='et_EE.UTF-8'
> LC_CTYPE='et_EE.UTF-8' OWNER="mydb_owner" ENCODING='UNICODE'
>
> 3. 2. CREATE DATABASE "mydb" WITH TEMPLATE=template0 LC_COLLATE='et_EE.UTF8'
> LC_CTYPE='et_EE.UTF8' OWNER="mydb_owner" ENCODING='UNICODE'
>
> in all cases same error
>
> invalid locale name
>
> occurs.
>
> Questions:
>
> 1. How to find correct locale name for Estonian in Debian  Linux ?
> In Windows case (1) works.

You can list available locales on your system with: locale -a

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom,

> You can list available locales on your system with: locale -a

Thank you.

How to resolve this issue if only 5432 port is open in server ?
Based on my limited knowledge, postgres does not have command to run
"locale -a"
How to get list of available locale names from Postgres ?
Why Postgres does not have command which returns available locale names ?
How to use same locale names in every platform?

Andrus.

On 01/12/2010 08:26, Andrus wrote:
> Thom,
>
>> You can list available locales on your system with: locale -a
>
> Thank you.
>
> How to resolve this issue if only 5432 port is open in server ?
> Based on my limited knowledge, postgres does not have command to run
> "locale -a"
> How to get list of available locale names from Postgres ?

This is a *nix command, not a PostgreSQL one, so just enter it at the
Linux shell prompt.

> Why Postgres does not have command which returns available locale names ?

"show server_encoding;" will tell you the encoding of the database to
which you're currently connected. I'm not sure how you find out what's
available, but the docs may help:

http://www.postgresql.org/docs/9.0/static/charset.html

> How to use same locale names in every platform?

See the docs above.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

On ons, 2010-12-01 at 10:26 +0200, Andrus wrote:
> How to get list of available locale names from Postgres ?
> Why Postgres does not have command which returns available locale
> names ?
> How to use same locale names in every platform?

There is no portable operating system interface to get the names of all
locales.

> There is no portable operating system interface to get the names of all
> locales.

Thank you.
Why postgres does not use conditional directives like

#ifdef WINDOWS

...

#endif

#ifdef LINUX
..
#endif

to return list of available locales ?

Andrus.

On 1 Dec 2010, at 15:15, Andrus wrote:

>> There is no portable operating system interface to get the names of all
>> locales.
>
> Thank you.
> Why postgres does not use conditional directives like
>
> #ifdef WINDOWS
> ...
> #endif
>
> #ifdef LINUX
> ..
> #endif
>
> to return list of available locales ?

Because, as opposed to the developers of the projects that you're apparently familiar with, the Postgres developers have common sense ;)

Seriously though, functionality like that has no place in a database server. That's the responsibility of the OS, or if it lacks in that respect, for third-party tools.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4cf698ed802651815816680!

Alban,

> Seriously though, functionality like that has no place in a database
> server. That's the responsibility of the OS, or if it lacks in that
> respect, for third-party tools.

Thank you.
So only portable way is to probe Postgres 9 locales using CREATE DATABASE
command with expected locale names hoping that some call suceeds?

Andrus.

On 1 Dec 2010, at 21:46, Andrus wrote:

> Alban,
>
>> Seriously though, functionality like that has no place in a database server. That's the responsibility of the OS, or if it lacks in that respect, for third-party tools.
>
> Thank you.
> So only portable way is to probe Postgres 9 locales using CREATE DATABASE command with expected locale names hoping that some call suceeds?

Well... if you really have to insist on using Postgres to determine system locales, I suppose you could do that - just like you can use a crane to park your car. It's not a very sensible thing to attempt before looking at alternative solutions though.

I imagine there must be tools for that purpose, or otherwise some programming language will probably have functions for this that you can use. Using the database server for this would be the last thing I'd try.
I can't say I'm too well-versed in locale usage though, I usually just stick with English.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4cf6cd4e802658319426940!

On 12/02/2010 02:49 AM, Alban Hertroys wrote:

> Because, as opposed to the developers of the projects that you're apparently familiar with, the Postgres developers have common sense ;)
>
> Seriously though, functionality like that has no place in a database server. That's the responsibility of the OS, or if it lacks in that respect, for third-party tools.

I can't say I entirely agree with that. PostgreSQL exposes locale names
for input, and it'd be helpful for users to have a way to determine what
the range of accepted input values is. If it was as simple as providing
a proc to list supported locale names, I'd say it'd be worth a few port/
files. However:

Knowing which values are acceptable locale names still doesn't help you
understand what they mean and what their consequences are. You'd also
need a way to get charset names at minimum - and not all OS-supported
charsets are supported by Pg. Pg's confusing "half-in-the-OS,
half-in-the-database-server" charset/collation/encoding handling doesn't
simplify things here.

An alternative would be to provide standard, portable names in Pg and
translate them to OS-specific names. Nobody sane would want to go there
given the choice, though, given that locales on different OSes are
rarely perfectly equivalent. The WINxxx and CPxxx encodings vs LATIN-xx
encodings are only one stumbling point; there are also issues with
differing TZDATA and DST rules and all sorts of other horrors.

So: Listing locales, probably filtered by supported charset(s), would be
nice, but given Pg's current partial dependence on the OS for handling
charset and locale issues, might be as confusing as it is helpful. If Pg
ever gets full locale and collation support internally it'd be a no-brainer.

(Of course, if Pg does its own collation and locale handling later
there'll be issues with inconsistencies between OS and Pg handling.
Argh! You cannot win.)

--
Craig Ringer

On 2010-12-01 09.26, Andrus wrote:
> Thom,
>
>> You can list available locales on your system with: locale -a
>
> Thank you.
>
> How to resolve this issue if only 5432 port is open in server ?

Maybe asking the sysadmin of that host to return the result of "locale -a"?

Btw, are you sure et_EE.UTF-8 is installed on the host?

> Based on my limited knowledge, postgres does not have command to run
> "locale -a"
> How to get list of available locale names from Postgres ?
> Why Postgres does not have command which returns available locale names ?
> How to use same locale names in every platform?
>
> Andrus.
>
>

--
Regards,
Robert "roppert" Gravsjö

>> Because, as opposed to the developers of the projects that you're
>> apparently familiar with, the Postgres developers have common sense ;)

intidb fallbacks to en_US locale if locale specified in command line is not
found.
This causes invalid cluster to be created.
How to fix this ?
Is this really common sense ?

>> Seriously though, functionality like that has no place in a database
>> server. That's the responsibility of the OS, or if it lacks in that
>> respect, for third-party tools.

Using CREATE DATABASE to determine server locale seems not reasonable since
this command
can fail for other reasons also. There is no way to know was this failing
due to missing locale.

What is best command to probe postgresql 9 server for locale existence ?

Andrus.

On 2 Dec 2010, at 13:34, Andrus wrote:

>>> Because, as opposed to the developers of the projects that you're
>>> apparently familiar with, the Postgres developers have common sense ;)
>
> intidb fallbacks to en_US locale if locale specified in command line is not
> found.

Aren't you confusing different commands now? You were talking about CREATE DATABASE, but now you're suddenly saying initdb.
And if you can run initdb, you can also run locale -a

Locales are an operating system matter, just like disk space usage, user names, cpu load, etc. A database server has no responsibility to provide such information - it could even lead to security issues if it did.

Not that I'm saying that a database server _couldn't_ provide such information, it would probably be quite useful to have some "virtual tables" for available locales, logged in users, kernel tunables, etc (provided the security implications are taken care of properly, of course!). We just don't have them now, and I don't think any other database has something like this either.

>>> Seriously though, functionality like that has no place in a database
>>> server. That's the responsibility of the OS, or if it lacks in that
>>> respect, for third-party tools.
>
> Using CREATE DATABASE to determine server locale seems not reasonable since this command
> can fail for other reasons also. There is no way to know was this failing due to missing locale.
>
> What is best command to probe postgresql 9 server for locale existence ?

You're obviously leaving out some important information, for example why you insist on using the database server to determine available locales. Is that even what you're trying to do, determine available locales?

Judging from your earlier answer that only port 5432 is open it would appear that you don't have shell access to the database server. But then you're suddenly talking about initdb, for which you do need shell access. You're not making a lot of sense with the little information you gave us.

Anyway, if CREATE DATABASE fails because the locale isn't available, it says so:

postgres=> CREATE DATABASE asdf LC_CTYPE 'as_DF';
ERROR: invalid locale name as_DF

So you certainly do know why it failed.

If you indeed don't have shell access but only database access, then you're probably pretty much stuck with just trying to create the database and checking the error message.

However, if the locale that your database needs isn't available, what did you intend to do about that? You can't just pick a different one, it probably wouldn't behave the same. Apparently you can't just use the default locales either, or you wouldn't be asking about this.

If the locale you need isn't available on the server and you don't have shell access to it, then you'll have to contact the server administrators to _make_ the locale available.
That means that either the OS doesn't have the required locale, or the database cluster was initialised with an incompatible default locale - neither of these situations can be solved by trying CREATE DATABASE with a different locale, not if the locale mattered in the first place.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4cf7b25e802651003785035!

> However, if the locale that your database needs isn't available, what did
> you intend to do about that? You can't just pick a different one, it
> probably wouldn't behave the same. Apparently you can't just use the
> default locales either, or you wouldn't be asking about this.

I'm trying to create portable application which can automatically create
database using estonian locale in any server.
Postgres returns different values for same locale:

In Fedora et_EE.UTF8
Other linuxes et_EE.UTF-8
In Windows Estonian_Estonia.1257
In some other Linuxes something like Estonian.Estonia

For this reason I'm looking for a way to probe server for locale existence.

> If the locale you need isn't available on the server and you don't have
> shell access to it, then you'll have to contact the server administrators
> to _make_ the locale available.
> That means that either the OS doesn't have the required locale, or the
> database cluster was initialised with an incompatible default locale -
> neither of these situations can be solved by trying CREATE DATABASE with a
> different locale, not if the locale mattered in the first place.

My locale may be available but it can have at least four different values
depending on server.
No need to disturb admins.

Andrus.

On 2 Dec 2010, at 18:53, Andrus wrote:

>> However, if the locale that your database needs isn't available, what did you intend to do about that? You can't just pick a different one, it probably wouldn't behave the same. Apparently you can't just use the default locales either, or you wouldn't be asking about this.
>
> I'm trying to create portable application which can automatically create database using estonian locale in any server.
> Postgres returns different values for same locale:
>
> In Fedora et_EE.UTF8
> Other linuxes et_EE.UTF-8

These are the same locales, just a differently named encoding.

> In Windows Estonian_Estonia.1257
> In some other Linuxes something like Estonian.Estonia

What do you need the locale for in your case? Collation? Monetary values? Numeric representation?
Or just for encoding - which isn't part of the locale AFAIK?

> For this reason I'm looking for a way to probe server for locale existence.

I'm thinking you may be able to use your clients settings to determine the right locale from the database's session variables. You can, for example, "set lc_ctype to 'et_EE';" and verify that you get the same locale back. Or you could perform a simple test-query that should return a row encoded in the encoding you need and verify that it matches what you expect.

I can't say for certain that this will work, my database is in C locale, and besides, I don't think my OS supports collation. You can just try it out and see what you get on different systems.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4cf7e5ea802651502012066!

>> In Fedora et_EE.UTF8
>> Other linuxes et_EE.UTF-8
>These are the same locales, just a differently named encoding.

Postgres requires those suffixes in locale names.
They must me exactly UTF8 or UTF-8 or 1257 .
If those are missing, or even if - sign is missed in UTF-8, error occurs on

create database test lc_collate '...'

So all those invariants must be used in probing.

> What do you need the locale for in your case?

I need locale mostly for ILIKE and ORDER BY to work properly.

>Collation?

Yes.

> Monetary values?

No

> Numeric representation?

No

> Or just for encoding - which isn't part of the locale AFAIK?
>> For this reason I'm looking for a way to probe server for locale
>> existence.
>
>
> I'm thinking you may be able to use your clients settings to determine the
> right locale from the database's session variables. You can, for example,
> "set lc_ctype to 'et_EE';" and verify that you get the same locale back.

set lc_ctype to 'et_EE'

or to other value returns

parameter "lc_ctype" cannot be changed

> Or you could perform a simple test-query that should return a row encoded
> in the encoding you need and verify that it matches what you expect.

I need to know locale name to set locale.
Is there some simple command which can used to test for locale existence for
create database command ?

Andrus.

On tor, 2010-12-02 at 19:53 +0200, Andrus wrote:
> I'm trying to create portable application which can automatically
> create
> database using estonian locale in any server.
> Postgres returns different values for same locale:
>
> In Fedora et_EE.UTF8
> Other linuxes et_EE.UTF-8
> In Windows Estonian_Estonia.1257
> In some other Linuxes something like Estonian.Estonia
>
> For this reason I'm looking for a way to probe server for locale
> existence.

Well, the solution here would be that someone sits down and creates a
universal taxonomy of all possible locale names in all possible
environments. Until someone embarks on that task, you will probably be
better off just tracking the handful of possible locale names applicable
to your concern yourself.