Alvaro Herrera <[EMAIL PROTECTED]> 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.
There is another way to broke indexes, like specify another user-defined template database in CREATE DATABASE.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.
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: [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
