Re: [PATCHES] Patch for database locale settings

> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)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.
There is another way to broke indexes, like specify another user-defined
template database in CREATE DATABASE.

I think, that we can add new parameter, like LOCALEDEPEND, in CREATE TYPE
syntax. It allows to separate locale-dependent indexes and reindex them. But I
havn't yet any idea, how we can reindex database immediately after creation. Any
suggestions ?

>
> 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
>
>

Alexey Slynko
E-mail: slynko(at)tronet(dot)ru