Thread: pg_dump and money type

pg_dump and money type

From
Peter Eisentraut
Date:
Since the money type has a locale dependent input and output format, there has 
to be some context saved when a database dump is created.  For example, if 
your environment uses a locale that uses the opposite point-vs-comma 
conventions from English (e.g., de_DE), then the following will fail to 
replicate the regression test database:

pg_dump regression | psql foo

The database regression has lc_monetary = C set, so this will produce C output 
piped into, say, de_DE input.

The first problem appears to be that pg_dump --create ought to save the 
database-specific configuration settings.  pg_dumpall gets this right.  But 
secondly, lc_monetary ought to be saved at the top of the dump file, much 
like client_encoding.  Unfortunately, that would probably break portability 
of dump files between different operating systems.  Perhaps we can get away 
with fixing --create and documenting this.  But something ought to be done 
about this; otherwise using the money type introduces a risk of breaking 
backup or upgrade procedures.

Comments?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: pg_dump and money type

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Since the money type has a locale dependent input and output format, there has 
> to be some context saved when a database dump is created.  For example, if 
> your environment uses a locale that uses the opposite point-vs-comma 
> conventions from English (e.g., de_DE), then the following will fail to 
> replicate the regression test database:

> pg_dump regression | psql foo

> The database regression has lc_monetary = C set, so this will produce C output 
> piped into, say, de_DE input.

> The first problem appears to be that pg_dump --create ought to save the 
> database-specific configuration settings.  pg_dumpall gets this right.  But 
> secondly, lc_monetary ought to be saved at the top of the dump file, much 
> like client_encoding.  Unfortunately, that would probably break portability 
> of dump files between different operating systems.  Perhaps we can get away 
> with fixing --create and documenting this.  But something ought to be done 
> about this; otherwise using the money type introduces a risk of breaking 
> backup or upgrade procedures.

This risk seems rather overstated, as it's unlikely that someone using
money would choose to reload their data into a DB with a fundamentally
incompatible locale setting.  They might, however, move to a different
platform that spells the name of that locale differently --- so I concur
that adding an lc_monetary setting to pg_dump output is likely to be
a cure worse than the disease.

My inclination is to do nothing except perhaps document the issue
someplace.  But since we've never heard any actual user complaints
about it, how real is the issue?
        regards, tom lane


Re: pg_dump and money type

From
"Heikki Linnakangas"
Date:
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> Since the money type has a locale dependent input and output format, there has 
>> to be some context saved when a database dump is created.  For example, if 
>> your environment uses a locale that uses the opposite point-vs-comma 
>> conventions from English (e.g., de_DE), then the following will fail to 
>> replicate the regression test database:
> 
>> pg_dump regression | psql foo
> 
>> The database regression has lc_monetary = C set, so this will produce C output 
>> piped into, say, de_DE input.
> 
>> The first problem appears to be that pg_dump --create ought to save the 
>> database-specific configuration settings.  pg_dumpall gets this right.  But 
>> secondly, lc_monetary ought to be saved at the top of the dump file, much 
>> like client_encoding.  Unfortunately, that would probably break portability 
>> of dump files between different operating systems.  Perhaps we can get away 
>> with fixing --create and documenting this.  But something ought to be done 
>> about this; otherwise using the money type introduces a risk of breaking 
>> backup or upgrade procedures.
> 
> This risk seems rather overstated, as it's unlikely that someone using
> money would choose to reload their data into a DB with a fundamentally
> incompatible locale setting.  

It doesn't sound unlikely at all to me. For example, people often use
C-locale for performance reasons, or because of ignorance of locale
issues. One scenario that seems particularly likely is to initialize and
load a database with en_US or C locale, and run like that for a few
weeks. After that, you notice that something's wrong, strings are sorted
in a funny way, etc. You realize that you're using the wrong locale, so
you take a backup with pg_dump, re-initdb with correct locale, and restore.

I haven't been following this thread closely; is there a work-around?

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


Re: pg_dump and money type

From
Tom Lane
Date:
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Tom Lane wrote:
>> This risk seems rather overstated, as it's unlikely that someone using
>> money would choose to reload their data into a DB with a fundamentally
>> incompatible locale setting.  

> It doesn't sound unlikely at all to me. For example, people often use
> C-locale for performance reasons, or because of ignorance of locale
> issues. One scenario that seems particularly likely is to initialize and
> load a database with en_US or C locale, and run like that for a few
> weeks. After that, you notice that something's wrong, strings are sorted
> in a funny way, etc. You realize that you're using the wrong locale, so
> you take a backup with pg_dump, re-initdb with correct locale, and restore.

If you're using type money, you will certainly have noticed whether it
spells the currency sign the way you like.  I can believe that someone
might go for a while with C where they should have used en_US, or vice
versa, but not that they'd have failed to notice the difference between
$ and DM, say.
        regards, tom lane


Re: pg_dump and money type

From
Alvaro Herrera
Date:
Tom Lane wrote:
> "Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> > Tom Lane wrote:
> >> This risk seems rather overstated, as it's unlikely that someone using
> >> money would choose to reload their data into a DB with a fundamentally
> >> incompatible locale setting.  
> 
> > It doesn't sound unlikely at all to me. For example, people often use
> > C-locale for performance reasons, or because of ignorance of locale
> > issues. One scenario that seems particularly likely is to initialize and
> > load a database with en_US or C locale, and run like that for a few
> > weeks. After that, you notice that something's wrong, strings are sorted
> > in a funny way, etc. You realize that you're using the wrong locale, so
> > you take a backup with pg_dump, re-initdb with correct locale, and restore.
> 
> If you're using type money, you will certainly have noticed whether it
> spells the currency sign the way you like.  I can believe that someone
> might go for a while with C where they should have used en_US, or vice
> versa, but not that they'd have failed to notice the difference between
> $ and DM, say.

Well, that assumes that countries use different signs.  We for example
use $, as does Argentina (but they add two decimal places where we have
none), Mexico and US.  And while both Arg. and Mexico use $ and 2
decimal places, the former uses "," for decimals while the latter
follows the US lead and uses ".".

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support