Re: Some 8.4 changes needed according to pg_migrator testing

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)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