[WIP] collation support revisited (phase 1)

Hi,

after long discussion with Mr. Kotala, we've decided to redesign our
collation support proposal.
For those of you who aren't familiar with my WIP patch and comments from
other hackers here's the original mail:
http://archives.postgresql.org/pgsql-hackers/2008-07/msg00019.php

In a few sentences - I'm writing collation support for PostgreSQL that is
almost independent on used collating function. I will implement POSIX
locales but switch to ICU will be quite easy. Collations and character sets
defined by SQL standard will be hard coded so we avoid non-existence in some
functions.

The whole project will be divided into two phases:

phase 1
Implement "sort of framework" so the PostgreSQL will have basic guts
(pg_collation & pg_charset catalogs, CREATE COLLATION, add collation support
for each type needed) and will support collation at database level. This
phase has been accepted as a Google Summer of Code project.

phase 2
Implement the rest - full collation at column level. I will continue working
on this after finishing phase one and it will be my master degree thesis.

How will the first part work?

Catalogs
- new catalogs pg_collation and pg_charset will be defined
- pg_collation and pg_charset will contain SQL standard collations +
optional default collation (when set other than SQL standard one)
- pg_type, pg_attribute, pg_namespace will be extended with references to
default records in pg_collation and pg_charset

initdb
- pg_collation & pg_charset will contain each pre-defined records regarding
SQL standard and optionally one record that will be non-standard set when
creating initdb (the one using system locales)
- these two records will be referenced by pg_type, pg_attribute,
pg_namespace in concerned columns and will be concidered as default
collation that will be inherited

CREATE DATABASE ... COLLATE ...
- after copying the new database the collation will be default (same as
cluster collation) or changed by COLLATE statement. Then we update pg_type,
pg_attribute and pg_namespace catalogs
- reindex database

When changing databases the database collation will be retrieved from type
text from pg_type. This part should be the only one that will be deleted
when proceeding with phase 2. But that will take a while :-)

Thanks for all your comments

Regards

Radek Strnad

On Thu, Jul 10, 2008 at 11:24:29PM +0200, Radek Strnad wrote:
> Hi,
>
> after long discussion with Mr. Kotala, we've decided to redesign our
> collation support proposal.
> For those of you who aren't familiar with my WIP patch and comments from
> other hackers here's the original mail:
> http://archives.postgresql.org/pgsql-hackers/2008-07/msg00019.php

<snip>
> phase 1
> Implement "sort of framework" so the PostgreSQL will have basic guts
> (pg_collation & pg_charset catalogs, CREATE COLLATION, add collation support
> for each type needed) and will support collation at database level. This
> phase has been accepted as a Google Summer of Code project.

Why bother with pg_charset? I don't think I've seen anyone even asking
for multiple charsets in a single DB and certainly no actual use case.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout napsal(a):
> On Thu, Jul 10, 2008 at 11:24:29PM +0200, Radek Strnad wrote:
>> Hi,
>>
>> after long discussion with Mr. Kotala, we've decided to redesign our
>> collation support proposal.
>> For those of you who aren't familiar with my WIP patch and comments from
>> other hackers here's the original mail:
>> http://archives.postgresql.org/pgsql-hackers/2008-07/msg00019.php
>
> <snip>
>> phase 1
>> Implement "sort of framework" so the PostgreSQL will have basic guts
>> (pg_collation & pg_charset catalogs, CREATE COLLATION, add collation support
>> for each type needed) and will support collation at database level. This
>> phase has been accepted as a Google Summer of Code project.
>
> Why bother with pg_charset? I don't think I've seen anyone even asking
> for multiple charsets in a single DB and certainly no actual use case.

The example is when you have translation data (vocabulary) in database. But the
reason is that ANSI specify (chapter 4.2) charset as a part of string
descriptor. See below:

— The length or maximum length in characters of the character string type.
— The catalog name, schema name, and character set name of the character set of
the character string type.
— The catalog name, schema name, and collation name of the collation of the
character string type.

If I think about it, the question is if we need both information in pg_type and
so on, because collation automatically define charset.

any idea?

Zdenek

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

Zdenek Kotala escribió:

> The example is when you have translation data (vocabulary) in database.
> But the reason is that ANSI specify (chapter 4.2) charset as a part of
> string descriptor. See below:
>
> — The length or maximum length in characters of the character string type.
> — The catalog name, schema name, and character set name of the character
> set of the character string type.
> — The catalog name, schema name, and collation name of the collation of
> the character string type.

We already support multiple charsets, and are able to do conversions
between them. The set of charsets is hardcoded and it's hard to make a
case that a user needs to create new ones. I concur with Martijn's
suggestion -- there's no need for this to appear in a system catalog.

Perhaps it could be argued that we need to be able to specify the
charset a given string is in -- currently all strings are in the server
encoding (charset) which is fixed at initdb time. Making the system
support multiple server encodings would be a major undertaking in itself
and I'm not sure that there's a point.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera napsal(a):
> Zdenek Kotala escribió:
>
>> The example is when you have translation data (vocabulary) in database.
>> But the reason is that ANSI specify (chapter 4.2) charset as a part of
>> string descriptor. See below:
>>
>> — The length or maximum length in characters of the character string type.
>> — The catalog name, schema name, and character set name of the character
>> set of the character string type.
>> — The catalog name, schema name, and collation name of the collation of
>> the character string type.
>
> We already support multiple charsets, and are able to do conversions
> between them. The set of charsets is hardcoded and it's hard to make a
> case that a user needs to create new ones. I concur with Martijn's
> suggestion -- there's no need for this to appear in a system catalog.
>
> Perhaps it could be argued that we need to be able to specify the
> charset a given string is in -- currently all strings are in the server
> encoding (charset) which is fixed at initdb time. Making the system
> support multiple server encodings would be a major undertaking in itself
> and I'm not sure that there's a point.
>

Background:
We specify encoding in initdb phase. ANSI specify repertoire, charset, encoding
and collation. If I understand it correctly, then charset is subset of
repertoire and specify list of allowed characters for language->collation.
Encoding is mapping of character set to binary format. For example for Czech
alphabet(charset) we have 6 different encoding for 8bit ASCII, but on other side
for UTF8 there is specified multi charsets.

I think if we support UTF8 encoding, than it make sense to create own charsets,
because system locales could have defined collation for that. We need conversion
only in case when client encoding is not compatible with charset and conversion
is not defined.

Any comments?

Zdenek

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

On Sat, Jul 12, 2008 at 10:02:24AM +0200, Zdenek Kotala wrote:
> Background:
> We specify encoding in initdb phase. ANSI specify repertoire, charset,
> encoding and collation. If I understand it correctly, then charset is
> subset of repertoire and specify list of allowed characters for
> language->collation. Encoding is mapping of character set to binary format.
> For example for Czech alphabet(charset) we have 6 different encoding for
> 8bit ASCII, but on other side for UTF8 there is specified multi charsets.

Oh, so you're thinking of a charset as a sort of check constraint. If
your locale is turkish and you have a column marked charset ASCII then
storing lower('HI') results in an error.

A collation must be defined over all possible characters, it can't
depend on the character set. That doesn't mean sorting in en_US must do
something meaningful with japanese characters, it does mean it can't
throw an error (the usual procedure is to sort on unicode point).

> I think if we support UTF8 encoding, than it make sense to create own
> charsets, because system locales could have defined collation for that. We
> need conversion only in case when client encoding is not compatible with
> charset and conversion is not defined.

The problem is that locales in POSIX are defined on an encoding, not a
charset. In locale en_US.UTF-8 doesn't actually sort any differently
than en_US.latin1, it's just that japanese characters are not
representable in the latter.

locale-gen can create a locale for any pair of (locale code,encoding),
whether the result is meaningful is another question.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)COM> writes:
> I think if we support UTF8 encoding, than it make sense to create own charsets,
> because system locales could have defined collation for that.

Say what? I cannot imagine a scenario in which a user-defined encoding
would be useful. The amount of infrastructure you need for a new
encoding is so large that providing management commands is just silly
--- anyone who can create the infrastructure can do the last little bit
for themselves. The analogy to index access methods is on point, again.

regards, tom lane

I was trying to sort out the problem with not creating new catalog for
character sets and I came up following ideas. Correct me if my ideas are
wrong.

Since collation has to have a defined character set I'm suggesting to use
already written infrastructure of encodings and to use list of encodings in
chklocale.c. Currently databases are not created with specified character
set but with specified encoding. I think instead of pointing a record in
collation catalog to another record in character set catalog we might use
only name (string) of the encoding.

So each collation will be set over these encodings set in chklocale.c. Each
database will be able to use only collations that are created over same
("compatible") encodings regarding encoding_match_list. Each standard
collation (SQL standard) will be defined over all possible encodings
(hard-coded).

Comments?

Regards

Radek Strnad

On Sat, Jul 12, 2008 at 5:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)COM> writes:
> > I think if we support UTF8 encoding, than it make sense to create own
> charsets,
> > because system locales could have defined collation for that.
>
> Say what? I cannot imagine a scenario in which a user-defined encoding
> would be useful. The amount of infrastructure you need for a new
> encoding is so large that providing management commands is just silly
> --- anyone who can create the infrastructure can do the last little bit
> for themselves. The analogy to index access methods is on point, again.
>
> regards, tom lane
>

On Mon, Jul 21, 2008 at 03:15:56AM +0200, Radek Strnad wrote:
> I was trying to sort out the problem with not creating new catalog for
> character sets and I came up following ideas. Correct me if my ideas are
> wrong.
>
> Since collation has to have a defined character set.

Not really. AIUI at least glibc and ICU define a collation over all
possible characters (ie unicode). When you create a locale you take a
subset and use that. Think about it: if you want to sort strings and
one of them happens to contain a chinese charater, it can't *fail*.
Note strcoll() has no error return for unknown characters.

> I'm suggesting to use
> already written infrastructure of encodings and to use list of encodings in
> chklocale.c. Currently databases are not created with specified character
> set but with specified encoding. I think instead of pointing a record in
> collation catalog to another record in character set catalog we might use
> only name (string) of the encoding.

That's reasonable. From an abstract point of view collations and
encodings are orthoginal, it's only when you're using POSIX locales
that there are limitations on how you combine them. I think you can
assume a collation can handle any characters that can be produced by
encoding.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout napsal(a):
> On Sat, Jul 12, 2008 at 10:02:24AM +0200, Zdenek Kotala wrote:
>> Background:
>> We specify encoding in initdb phase. ANSI specify repertoire, charset,
>> encoding and collation. If I understand it correctly, then charset is
>> subset of repertoire and specify list of allowed characters for
>> language->collation. Encoding is mapping of character set to binary format.
>> For example for Czech alphabet(charset) we have 6 different encoding for
>> 8bit ASCII, but on other side for UTF8 there is specified multi charsets.
>
> Oh, so you're thinking of a charset as a sort of check constraint. If
> your locale is turkish and you have a column marked charset ASCII then
> storing lower('HI') results in an error.

Yeah, if you use strcoll function it fails when illegal character is found.
See
http://www.opengroup.org/onlinepubs/009695399/functions/strcoll.html

> A collation must be defined over all possible characters, it can't
> depend on the character set. That doesn't mean sorting in en_US must do
> something meaningful with japanese characters, it does mean it can't
> throw an error (the usual procedure is to sort on unicode point).

Collation cannot be defined on any character. There is not any relation between
Latin and Chines characters. Collation has sense when you are able to specify <
= > operators.

If you need compare Japanese and Latin characters then ansi specify default
collation for each repertoire. I think it is usually bitwise comparing.

Zdenek

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

Tom Lane napsal(a):
> Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)COM> writes:
>> I think if we support UTF8 encoding, than it make sense to create own charsets,
>> because system locales could have defined collation for that.
>
> Say what? I cannot imagine a scenario in which a user-defined encoding
> would be useful.

I did not mean user defined encoding but user defined charset. For example
cs_CZ(dot)UTF-8(at)euro locale uses UTF8 encoding and collation is defined on czech
charset which specifies list of allowed character. If somebody will have
installed e.g. Turkish locale then he will want to have also Turkish charset in
postgres. I guess, Charset also defines how upper/lower case will work (see
i/I in Turkish).

Please, correct me if I wrong.

thanks Zdenek

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

Martijn van Oosterhout napsal(a):
> On Mon, Jul 21, 2008 at 03:15:56AM +0200, Radek Strnad wrote:
>> I was trying to sort out the problem with not creating new catalog for
>> character sets and I came up following ideas. Correct me if my ideas are
>> wrong.
>>
>> Since collation has to have a defined character set.
>
> Not really. AIUI at least glibc and ICU define a collation over all
> possible characters (ie unicode). When you create a locale you take a
> subset and use that. Think about it: if you want to sort strings and
> one of them happens to contain a chinese charater, it can't *fail*.
> Note strcoll() has no error return for unknown characters.

It has.
See http://www.opengroup.org/onlinepubs/009695399/functions/strcoll.html

The strcoll() function may fail if:

[EINVAL]
[CX] The s1 or s2 arguments contain characters outside the domain of
the collating sequence.

>> I'm suggesting to use
>> already written infrastructure of encodings and to use list of encodings in
>> chklocale.c. Currently databases are not created with specified character
>> set but with specified encoding. I think instead of pointing a record in
>> collation catalog to another record in character set catalog we might use
>> only name (string) of the encoding.
>
> That's reasonable. From an abstract point of view collations and
> encodings are orthoginal, it's only when you're using POSIX locales
> that there are limitations on how you combine them. I think you can
> assume a collation can handle any characters that can be produced by
> encoding.

I think you are not correct. You cannot use collation over all UNICODE. See
http://www.unicode.org/reports/tr10/#Common_Misperceptions. Same characters can
be ordered differently in different languages.

Zdenek

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

On Tue, Jul 22, 2008 at 04:32:39PM +0200, Zdenek Kotala wrote:
> >Oh, so you're thinking of a charset as a sort of check constraint. If
> >your locale is turkish and you have a column marked charset ASCII then
> >storing lower('HI') results in an error.
>
> Yeah, if you use strcoll function it fails when illegal character is found.
> See
> http://www.opengroup.org/onlinepubs/009695399/functions/strcoll.html

Wierd, at least in glibc and ICU it can't happen but perhaps there are
other implementations where it can...

> Collation cannot be defined on any character. There is not any relation
> between
> Latin and Chines characters. Collation has sense when you are able to
> specify < = > operators.

There is no standardised relation. However, if your collation library
decides to define all chinese characters after all latin characters,
they will have defined a collation that will work for all strings with
any characters... Which is basically the approach glibc/ICU takes.

I think the standard is kind of pathetic to say that strcoll can set
errno but have no value to indicate error. I wonder how many platforms
actually use that "feature".

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.