[WIP] patch - Collation at database level

Radek Strnad escribi�:

2) What type should all names in CREATE and DROP statement in gram.y have?
I've chosen qualified_name but I know it's not the best choice.

I think it should be ColId.

3) All collations are created from existing collations. How do I ensure that
the collation already exists? Is there any possibility to define it in
gram.y?

Certainly not -- shouldn't they come from a catalog? In that case, it
must come in parse analysis (parser/analyze.c I guess) or perhaps later,
when you actually execute the function to create the new collation.

5) Also can you look at the pg_catalog and tell me if anything is wrong with
it?

Why does a collation have a schema?

What's the "existing collation"?

It seems a bit silly to have enum for what are basically boolean
variables. Why not just use "true" and "false"?

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

Alvaro Herrera <alvherre@commandprompt.com> writes:

Why does a collation have a schema?

Because the SQL spec says so. Also, if we don't put them in schemas,
we have no nice way to distinguish built-in and user-defined collations,
which creates a problem for pg_dump.

regards, tom lane

Tom Lane escribi�:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Why does a collation have a schema?

Because the SQL spec says so. Also, if we don't put them in schemas,
we have no nice way to distinguish built-in and user-defined collations,
which creates a problem for pg_dump.

Oh, I see :-) In that case, qualified_name would seem the right symbol
to use in the parser.

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

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Why does a collation have a schema?

Because the SQL spec says so. Also, if we don't put them in schemas,
we have no nice way to distinguish built-in and user-defined collations,
which creates a problem for pg_dump.

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!

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

On Wed, Jul 02, 2008 at 07:22:10PM +0100, Gregory Stark wrote:

Because the SQL spec says so. Also, if we don't put them in schemas,
we have no nice way to distinguish built-in and user-defined collations,
which creates a problem for pg_dump.

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!

Not that we'll ever use it, but ICU for example allows users to say:
"use collation X but move this code point somewhere else", essentially
allowing users tweak the collation on a small scale. In any case,
whatever collation library is used, we're unlikely to predefine every
possible collation in the system, there's too many (assuming they're
denumerable).

Have a niceday,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

My patch should be "sort of" wrapper that will implement guts for further
development (collation at column level) like catalogs, creating collations
etc. When creating collation user will be able to choose which function to
use (by statement STRCOLFN - not in SQL standard). In the first stage I'll
implement function that will use system locales. Adding ICU or any other
library won't be that big deal.

Radek Strnad

On Wed, Jul 2, 2008 at 8:22 PM, Gregory Stark <stark@enterprisedb.com>
wrote:

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!

Hm, that's a good point. SQL99 has

<collation definition> ::=
CREATE COLLATION <collation name> FOR
<character set specification>
FROM <existing collation name>
[ <pad characteristic> ]

<existing collation name> ::= <collation name>

<pad characteristic> ::=
NO PAD
| PAD SPACE

which seems pretty stupid if you ask me --- all the mechanism required
to manage a new object type, just to enable PAD SPACE or not?
(Especially when PAD SPACE itself is an utterly broken, useless concept
... but I digress.) You might as well just provide all the standard
collations in both variants and be done with it.

The statement looks the same in last year's 200n draft, so it's not
like they were just about to add some more capability.

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.

regards, tom lane

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!

Hm, that's a good point. SQL99 has

<collation definition> ::=
CREATE COLLATION <collation name> FOR
<character set specification>
FROM <existing collation name>
[ <pad characteristic> ]

<existing collation name> ::= <collation name>

<pad characteristic> ::=
NO PAD
| PAD SPACE

which seems pretty stupid if you ask me --- all the mechanism required
to manage a new object type, just to enable PAD SPACE or not?
(Especially when PAD SPACE itself is an utterly broken, useless concept
... but I digress.) You might as well just provide all the standard
collations in both variants and be done with it.

The statement looks the same in last year's 200n draft, so it's not
like they were just about to add some more capability.

The proposed syntax of CREATE COLLATION is:

CREATE COLLATION <collation name> FOR <character set specification>
FROM <existing collation name> [STRCOLFN <fn name>]
[ <pad characteristic> ] [ <case sensitive> ] [ <accent sensitive> ]
[ LCCOLLATE <lc_collate> ] [ LCCTYPE <lc_ctype> ]

Which extends ANSI specification.

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 think you cannot create all collation at bootstrap. You can only create record
for actual LC_COLLATION, because I there is not standard way how to obtain
complete list of supported collations and there is also problem if you install
new locales after initdb.

When I looked to another DB (MS SQL, MySQL, DB2, Firebird) then only Firebird
supports CREATE COLLATION command. Other databases has hard coded list of
locales. Hardcoded solution means to use some lib (e.g ICU) with unified names
or has locale name mapping for all supported OS.

I personally prefer open solution when I can create own collation and specify
collation function to handle it.

Zdenek

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

Zdenek Kotala napsal(a):

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 :(.

thinking more ...
It must be shared catalog because pg_database will depend on it.

Zdenek

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

On Tue, Jul 08, 2008 at 11:27:35AM +0200, Zdenek Kotala wrote:

Zdenek Kotala napsal(a):

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 :(.

thinking more ...
It must be shared catalog because pg_database will depend on it.

Not necessarily. pg_class is not shared yet without it you can't even
find pg_database. Same deal with pg_type. All it means is that
pg_collation in template1 must contain all the collations used in
template1, which shouldn't be hard to arrange.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout napsal(a):

On Tue, Jul 08, 2008 at 11:27:35AM +0200, Zdenek Kotala wrote:

Zdenek Kotala napsal(a):

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 :(.

thinking more ...
It must be shared catalog because pg_database will depend on it.

Not necessarily. pg_class is not shared yet without it you can't even
find pg_database. Same deal with pg_type. All it means is that
pg_collation in template1 must contain all the collations used in
template1, which shouldn't be hard to arrange.

I think, Collation situation is different, becasue pg_database will contains
column colname. pg_class (and all bootstrap catalog) only contains row which
specify that shared table exists and content is cloned to the new database from
template database. In corner case you can get context specific dependency for
example if Czech collation will have oid=10 in database test01 and Swedish
collation will have oid=10 in database test02. How to handle CREATE DATABASE and
connect database? OK it shouldn't happen in normal situation but ...

Zdenek

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

On Tue, Jul 08, 2008 at 12:00:34PM +0200, Zdenek Kotala wrote:

Not necessarily. pg_class is not shared yet without it you can't even
find pg_database. Same deal with pg_type. All it means is that
pg_collation in template1 must contain all the collations used in
template1, which shouldn't be hard to arrange.

I think, Collation situation is different, becasue pg_database will
contains column colname. pg_class (and all bootstrap catalog) only contains
row which specify that shared table exists and content is cloned to the new
database from template database. In corner case you can get context
specific dependency for example if Czech collation will have oid=10 in
database test01 and Swedish collation will have oid=10 in database test02.
How to handle CREATE DATABASE and connect database? OK it shouldn't happen
in normal situation but ...

Oh I see, you're referring to the storage of the default collation for
a database. I was jumping ahead to the per-column collation state, when
the collation default is attached to columns, types and domains, and
not at the database level. So there the problem does not exist.

To be honest, I'd suggest storing the collation in pg_database as a
string, rather than as an identifier. This sidesteps the problem
entirly.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout napsal(a):

Oh I see, you're referring to the storage of the default collation for
a database. I was jumping ahead to the per-column collation state, when
the collation default is attached to columns, types and domains, and
not at the database level. So there the problem does not exist.

Yeah, but you still need one source/one collation list for database, scheme,
table and column. And of course shared tables need also collation for their indexes.

To be honest, I'd suggest storing the collation in pg_database as a
string, rather than as an identifier. This sidesteps the problem
entirly.

I don't think that string is good idea. You need to use same approach on all
levels by my opinion.

Zdenek

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

Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:

Martijn van Oosterhout napsal(a):

Not necessarily. pg_class is not shared yet without it you can't even
find pg_database. Same deal with pg_type. All it means is that
pg_collation in template1 must contain all the collations used in
template1, which shouldn't be hard to arrange.

I think, Collation situation is different,

All the argument here is based on the premise that we should have
database-level collation specifications, which AFAICS is not required
nor suggested by the SQL spec. I wonder why we are allowing a
nonstandard half-measure to drive our thinking, rather than solving the
real problem which is column-level collations.

regards, tom lane

Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:

... And of course shared tables need also collation for their indexes.

No, they don't, because the only textual indexes on shared catalogs are
on "name" columns, which are intentionally not locale aware, and
wouldn't be collation aware either.

regards, tom lane

Tom Lane wrote:

All the argument here is based on the premise that we should have
database-level collation specifications, which AFAICS is not required
nor suggested by the SQL spec. I wonder why we are allowing a
nonstandard half-measure to drive our thinking, rather than solving the
real problem which is column-level collations.

Agreed. Are we even sure that we want per-database collations as a
half-way house? Unless we can be sure that we want all the required
catalog changes for the full requirement, it seems to me a rather messy
way of getting to where we want to go.

cheers

andrew

On Tue, Jul 08, 2008 at 10:54:28AM -0400, Andrew Dunstan wrote:

Agreed. Are we even sure that we want per-database collations as a
half-way house? Unless we can be sure that we want all the required
catalog changes for the full requirement, it seems to me a rather messy
way of getting to where we want to go.

Given that the current projected timeframe for full COLLATE support is
something like 2-3 major releases, I don't really see the problem with
doing this now. Who knows, it might prompt people to do something
sooner.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Andrew, Tom,

Agreed. Are we even sure that we want per-database collations as a
half-way house? Unless we can be sure that we want all the required
catalog changes for the full requirement, it seems to me a rather messy
way of getting to where we want to go.

Given that we don't have a delivery date for table or column level collations,
we don't want to turn down database-level collations. If nothing else,
Radek's work will expose what areas of our code are collation-dependant and
hopefully make the work of more granular collations easier. And if it takes
us 3 years to get more granular collations, at least people can use
database-level ones in the meantime so that they don't need to have separate
PostgreSQL binaries for every language they want to support fully.

Also ... this is a Summer of Code Project, which we accepted, which at least
in Google and the student's eyes means we're not going to discard the entire
premise of the patch. I'm not exaggerating when I say doing something like
that could get PostgreSQL permanently banned from Google SoC.

Tom, I think you need to be on the SoC committee in the future, just to raise
objections. Some 15+ PostgreSQL contributors on the SoC committee approved
Radek's project.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco