pg_dump and money type

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

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

"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

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