I am trying to figure out which is the best way to store custom collation
tables on a PostgreSQL server system, and what kind of interface to
provide to users to allow them to create their own.

A collation table essentially consists of a mapping 'character code ->
weight' for every character in the set and some additional considerations
for one-to-many and many-to-one mappings, plus a few feature flags.

How would a user go about creating such a table?

CREATE COLLATION foo (
   ...
   <10000 lines of data>
   ...
);

or would it be preferrable to store the table in some external file and
then have the call simply be, say,

CREATE COLLATION foo SOURCE 'some file';

The latter has the disadvantage that we'd need some smarts so that pg_dump
would not repeat the mistakes that were made with dynamically loadable
modules (such as absolute file paths).  The former has the disadvantage
that it is too unwieldy to be useful.

We also need to consider the following two problems:

Firstly, if the collation data -- no matter how it is created -- is stored
within the database (that is, in some table(s)), then it would be
duplicated in every database.  Depending on the storage format, a
collation table takes between around 100 kB and 800 kB.  Multiply that by
a few dozen languages, for each database.  That would make an external
file seem more attractive.  (The external file would need to be a binary
file that is precomputed for efficient processing, unless we want to
reparse and reprocess it every so often, like for every session.)

Secondly, because each collation table depends on a particular character
encoding (since it is indexed by character code), some sort of magic needs
to happen when someone creates a database with a different encoding than
the template database.  One option is to do some mangling on the
registered external file name (such as appending the encoding name to the
file name).  Another option is to have the notional pg_collate system
catalog contain a column for the encoding, and then simply ignore all
entries pertaining to encodings other than the database encoding.

Comments or better ideas?

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 18 Nov 2002 19:10:12 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Ideas needed: How to create and store collation tables

On Mon, 18 Nov 2002, Peter Eisentraut wrote:

> A collation table essentially consists of a mapping 'character code ->
> weight' for every character in the set and some additional considerations
> for one-to-many and many-to-one mappings, plus a few feature flags.
>
> How would a user go about creating such a table?
>
> CREATE COLLATION foo (
>    ...
>    <10000 lines of data>
>    ...
> );
>
> or would it be preferrable to store the table in some external file and
> then have the call simply be, say,
>
> CREATE COLLATION foo SOURCE 'some file';

I'd say the latter makes more sense, but would it be better to use
CREATE COLLATION foo FROM EXTERNAL 'some file';
where we say valid implementation defined collation names are references
to files of the appropriate type?

> Secondly, because each collation table depends on a particular character
> encoding (since it is indexed by character code), some sort of magic needs
> to happen when someone creates a database with a different encoding than
> the template database.  One option is to do some mangling on the
> registered external file name (such as appending the encoding name to the
> file name).  Another option is to have the notional pg_collate system
> catalog contain a column for the encoding, and then simply ignore all
> entries pertaining to encodings other than the database encoding.

The SQL92 CREATE COLLATION seems to create a collation for a particular
character set, so the latter seems more appropriate to me, especially if
we plan to support the full range of SQL's character set/collation/padding
attributes at some point.


Mon, 18 Nov 2002 12:08:54 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Ideas needed: How to create and store collation tables
Peter Eisentraut <peter_e@gmx.net> writes:
> I am trying to figure out which is the best way to store custom collation
> tables on a PostgreSQL server system, and what kind of interface to
> provide to users to allow them to create their own.

> A collation table essentially consists of a mapping 'character code ->
> weight' for every character in the set and some additional considerations
> for one-to-many and many-to-one mappings, plus a few feature flags.

I'd be inclined to handle it similarly to the way that Tatsuo did with
conversion_procs: let collations be represented by comparison functions
that meet some suitable API.  I think that trying to represent such a
table as an SQL table compactly will be a nightmare, and trying to
access it quickly enough for reasonable performance will be worse.  Keep
the problem out of the API and let each comparison function do what it
needs to do internally.

> Secondly, because each collation table depends on a particular character
> encoding (since it is indexed by character code), some sort of magic needs
> to happen when someone creates a database with a different encoding than
> the template database.  One option is to do some mangling on the
> registered external file name (such as appending the encoding name to the
> file name).  Another option is to have the notional pg_collate system
> catalog contain a column for the encoding, and then simply ignore all
> entries pertaining to encodings other than the database encoding.

SQL92 says that any particular collation is applicable to only one
character set (which is their term that matches our "encoding"s).
So I think we'd definitely want to associate a character set with each
pg_collation entry, and then ignore any entries that don't match the 
DB encoding.  (Further down the road, "the" DB encoding might change
into just a "default for tables in this DB" encoding, meaning that we'd
need access to collations for multiple encodings anyway.)

			regards, tom lane

Mon, 18 Nov 2002 15:44:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Ideas needed: How to create and store collation tables 
> I'd be inclined to handle it similarly to the way that Tatsuo did with
> conversion_procs: let collations be represented by comparison functions
> that meet some suitable API.  I think that trying to represent such a
> table as an SQL table compactly will be a nightmare, and trying to
> access it quickly enough for reasonable performance will be worse.  Keep
> the problem out of the API and let each comparison function do what it
> needs to do internally.

Agreed. That was the way I have been thinking about collation/create
character stuff too.
--
Tatsuo Ishii

Tue, 19 Nov 2002 10:21:30 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Ideas needed: How to create and store collation