Re: [PATCHES] Patch for database locale settings

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Maybe it would work if we forced indexes on shared relations to be
> scanned using a fixed collation.

The shared relations only have indexes on name, oid, and integer:
select distinct atttypid::regtype
from pg_class c join pg_attribute a on c.oid = a.attrelid
where relisshared and relkind = 'i';
and name has non-locale-sensitive ordering rules anyway.  So that's
not the big problem; we could probably get away with decreeing that
name will always be that way and that shared relations can't have
locale-dependent indexes.

The big problem (and the reason why this idea has been shot down in
the past) is that CREATE DATABASE can't change the locale from what it
is in the template database unless it's prepared to reindex any locale-
sensitive indexes in the non-shared part of the template database.
Which would be a difficult undertaking seeing that we can't even connect
to the copied database until after commit.

We could maybe say that we will never have any locale-dependent indexes
at all on any system catalog, but what of user-defined tables in
template databases?  It would simply not work to do something as simple
as creating a table with an indexed text column in template1.

On the other hand you could argue that people already run the same kind
of risk when changing database encoding at CREATE, which is a feature
that's been there a long time and hasn't garnered many complaints.
Not so much that their indexes will break as that their data will.
So perhaps we should be willing to document "don't do that".  Certainly
it would be a lot more useful if both locale and encoding could be set
per-database.

            regards, tom lane