Re: Proposal for 2.0: New monetary data types

[Chris Travers <..hidden..>]

On Sun, Apr 4, 2010 at 7:11 PM, Adam Thompson <..hidden..> wrote:
>> Also all math within the function should be integer math, and the
>> return
>> result should be left as full precision real.
>> This will avoid all rounding error.
>> The result can then be rounded at application level.
>
> It's impossible (AFAIK) to avoid all rounding errors in monetary amounts
> as long as there continue to exist - anywhere in the world - irrational
> denominations.

Well, technically they are still rational.  We haven't yet seen 1/pi
denominations or the like ;-)

> I can't name any such systems *currently* in use, although the most
> obvious example is the Commonwealth (British) Shilling and Pence, set at
> 1s=12d.  (Abbr for pence is d, not p.)  If you want a fully generalizable
> monetary currency system, you probably also have to accommodate such
> bizzarities as the conversion between shillings and sheep :-)  (1 shilling
> = 1 sheep in Anglo-Saxon era, FYI... who needs a gold standard?!)  FWIW,
> the old French sou was worth 12 deniers (origin of "d" as abbreviation).

Hence we should probably stick with the tradeoff I was mentioning and
say "decimal only."

>
> If you want to retain rational mathematics, never mind integer math,
> you'll pretty much have to declare that the type explicitly supports only
> currencies in use from 1971-Feb-15 onward, although that's probably better
> phrased as:
>        * You should probably limit the type to decimalized currencies, and state
> that limitation explicitly

That's probably good enough, given the background of this subproject
(see below).
>
> Aside from historical curiosities, the more I read of this proposal, the
> more I smell feeping creaturism... increasingly, it doesn't sound like
> something that can reasonably be black-boxed into a custom data type;
> arbitrary monetary declaration, registration, etc., is an entire
> application's worth of data, or at least a major subsystem.  I would
> compare this to the TZ subsystem in unix-like OSes: the complexity quickly
> becomes insane once you start capturing temporal data.  As an example,
> what do you do with currencies that have been rebased or devalued, yet
> retain their ISO currency code?  The only solution I can see is to also
> require an "effective date" as part of the type definition.  If I want to
> compare two pieces of data denominated in the *same* currency (ignoring my
> previous case), since we have to store historical data (at least about
> exchange rates, anyway) does the comparison take into account the
> difference in a single currency's valuation?

Ok, so here is some additional background (not really relevant to the
implementation but may be of help in understanding why this proposal,
why now, why officially a separate project).

A couple weeks ago a discussion on the Pgsql-bugs list surfaced over
currency handling and the PostgreSQL money type.  The money type is
one of those strange beasts which is "deprecated pending enhancements"
(IOW not fully functional, best if you don't use it, but it will be
enhanced and further supported at some point) and after the discussion
went on a while it became clear that what a lot of people needed was
not simply an enhancement to what the money type does today but a very
good multicurrency type.  At least one other individual with a very
different set of applications needed has decided he needs something
similar.  So what I have been collaborating on is a two-part
enhancement for PostgreSQL:

Part 1 is a multicurrency monetary set of data types.  These include
basic operations (monetary + monetary, monetary + numeric,
monetary/monetary, monetary/numeric, and so forth).  There are a few
other functions to convert to numeric, to extract a currency, and to
represent as text.  A function to change between currencies at a
specified rate will be part of this module.  While I am involved in
the spec process I will probably not be writing this section (which
will probably be written in C).

Part 2 is a monetary subsystem for financial applications.  This will
include currency validation logic, date-based exchange-rate handling,
and the like.

Other collaborators maintain other PostgreSQL-backed projects which
have multicurrency requirements.  Based on this, my guess is that this
will be a separate Pg-foundry project licensed under the BSD license.

Two of the major reasons for trying to make this generally applicable include:

1)  Increasing collaboration with other projects (perhaps with luck
getting the data type/operator support included in PostgreSQL as a
contrib module eventually) and thus offloading it from the LedgerSMB
project, and
2)  Ensuring that other projects' requirements help show us where we
may be missing something.

Hope this helps,
Chris Travers