Re: Some 8.4 changes needed according to pg_migrator testing

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> On Thursday 07 May 2009 20:54:37 Alvaro Herrera wrote:
>>> I don't think there's much we can do apart from telling the user not to
>>> move stuff across platforms that do not have equally named locales.
> 
>> The other part of the problem is that there is no guarantee that equally or 
>> similarly named locales behave the same.  There will necessarily be a user-
>> beware factor here, and perhaps blowing up when the locale name is not 
>> recognized is safer and alerts about this fact better than trying to make it 
>> match by force somehow.
> 
> We have never before operated on the assumption that it's okay for
> pg_dump output to be locked to particular platforms, and I do not think
> we should start now.  So we've got to do *something* about this.
> (Do you really want PG Windows users to be unable to port to a better
> platform?)
> 
> I note also that the problem is in pg_dumpall not pg_dump, so pg_restore
> is not going to help us --- if someone has a problem his only recourse
> would be manual editing of a possibly-monstrous SQL script file.

How about only outputting the LC_COLLATE/CTYPE options for databases 
that use a non-default setting? In the common scenarios where you have 
the same collation for the whole cluster it would work just like in 
previous releases. If you dump and restore a database with default 
locale to a cluster initialized with a different locale, the database is 
restored with the default locale of the target cluster. But if you 
explicitly set a database to use a different locale, that would be 
preserved in dumps with the caveat that you'd have to change it manually 
if you restore to a cluster on a different platform.

If we go with that, we should probably make the notion of a default 
collation explicit. We could set pg_database.datcollate/datctype column 
to NULL to mean "use the cluster default".

I don't find the idea of creating mapping tables of locale names very 
appetizing. Looking at our encoding name mapping table, there's quite a 
few different spellings of different encoding names alone, let alone all 
locale names.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com