Setting up gnucash to use postgesql back end - close but no cigar ...

[John Ralls]

> On Mar 13, 2015, at 8:32 AM, AC <gnucash at acarver.net> wrote:
> 
> There are datatypes built into some databases that can support larger
> exact formats.  For example, MySQL has an exact decimal format that
> supports up to 65 places of precision (total of both left and right of
> the decimal point, see
> http://dev.mysql.com/doc/refman/5.6/en/fixed-point-types.html and the
> related documents on precision math
> http://dev.mysql.com/doc/refman/5.6/en/precision-math.html).  Internally
> the number is represented in a binary format that are encoded to remain
> exact.
> 
> The float type is the other large number format but it represents
> approximate values rather than exact and doesn't use the precision math
> libraries.
> 
> It may be a hard call to say that the decimal split be in a certain
> location.  For example, should the split be (7,58) which allows
> transactions up to an integer value of 9,999,999 (seven 9's) plus the
> remaining 58 positions of fractional value or do users need larger
> integer portions?  How many fractional positions are truly required?
> With the exception of a small number of currencies, financial systems
> already round off transactions at the moment of calculation and do not
> carry arbitrary precision through to completion (leading to the plot
> device of the Office Space movie).

Unfortunately SQLite3 doesn't support decimals, only floats. SQLite3 is the only F/LOSS serverless SQL implementation, so being able to use it is a hard requirement. We don't want to force the average user to have to deal with setting up a MySql server just to run GnuCash.

Floats, which use binary math, can't exactly represent 1/10 and are unacceptable for use with money. Decimal number classes use BCD, usually compressed so that a byte contains 3 decimal digits. Since few CPUs (and none of Intel's or ARM's) have hardware support for decimal math all calculations are significantly slower. Rational numbers use integer math for which there is abundant hardware support and which is exact across the full range of numbers, and it's well understood how to efficiently widen integer calculations when more digits are needed than the hardware supports directly.

What GnuCash needs for numeric representation is a bit squishy: A very wealthy Indonesian might need to represent 10**12, equivalent to 10**7USD, while bitcoin requires fractions of 10**-8; for rounding control we'd need a couple more fractional digits, 10**-10, so the full range would be 22 significant digits. I think it unlikely that anyone would be converting 10**12 Rupiah into bitcoin, so I'm comfortable with the 10**19 effective significant digits supported by the current (in master) rational implementation.

Regards,
John Ralls