Re: [WIP] patch - Collation at database level

Tom Lane napsal(a):
> Gregory Stark <stark@enterprisedb.com> writes:
>> Out of curiosity, what is a "user-defined collation"? Are there SQL statements
>> to go around declaring what order code points should be sorted in? That seems
>> like it would be... quite tedious!
> 

<snip>

> We might be best off to treat collations like index access methods,
> ie, they're theoretically add-able but there's no infrastructure for
> managing them, and what's expected is that all the ones you need are
> created by initdb.

I though more about it and I discussed it with Radek yesterday. The problem is 
that collation must be created before user want to use CREATE DATABASE ... 
COLLATE ... command. It inclines to have have pg_collation as a global catalog, 
but ANSI specifies to use schema name in collation specification and schemes are 
database specific ... It means that pg_collation have to be non-shared catalog 
and new database only inherits collation from template db. And CREATE DATABASE 
have to check list of collation in template database :(.

My conclusion is that CREATE COLLATION does not make much sense. I see two 
possible solutions:

1) have global an local catalog for collation and have modified variants of 
create cmd:
   CREATE COLLATION ... GLOBAL|LOCAL
   CREATE DATABASE will use only collation from global catalog

Local catalog will be useful when full support of collation will be available 
mostly for specifying case sensitivity of collation.


2) Use Tom's suggested approach. Create list of collations in initdb phase. But 
there is problem how to obtain list of supported collation on the server. I 
think, only what is possible to do is to use default locale for creating default 
collation for template1 database.

Any suggestion?
    thanks Zdenek


-- 
Zdenek Kotala              Sun Microsystems
Prague, Czech Republic     http://sun.com/postgresql