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?

Search Discussions

  • Tom Lane at Sep 11, 2007 at 7:42 pm

    Peter Eisentraut 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
  • Heikki Linnakangas at Sep 11, 2007 at 8:44 pm

    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
  • Tom Lane at Sep 12, 2007 at 12:59 am

    "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
  • Alvaro Herrera at Sep 12, 2007 at 1:35 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 11, '07 at 2:40p
activeSep 12, '07 at 1:35a
posts5
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase