Thread: pg_dump and money type
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/
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