Re: [WIP] patch - Collation at database level

Tom Lane napsal(a):
> Gregory Stark <stark(at)enterprisedb(dot)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