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

AC gnucash at acarver.net
Thu Mar 12 19:32:57 EDT 2015


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).

On 2015-03-12 16:11, Edward Doolittle wrote:
> I suspect the problem is that most (all?) libraries for decimal numbers don't do rational arithmetic exactly. If you pay $7 for 6 Euros (or is that $6 for 7 Euros? :-) neither conversion factor 6/7 nor 7/6 is an exact decimal ... Infinite precision decimal arithmetic, not arbitrary precision, is needed for exact results. GnuCash now does a lovely job handling such conversion rates as rational numbers; I presume (and am interested to know for sure) that internally GnuCash uses rational numbers as much as possible, leaving decimal approximations to the last moment.
> 
> Decimals can of course be used as approximations, which may be adequate depending on the exact requirements, but I can appreciate how hard it would be to find a decimal library that works given GnuCash's requirements for internationalization and other exacting requirements. (I would be disconcerted if my balance sheet didn't balance due to rounding errors, for example.)
> 
> I know next to nothing about databases, but I imagine it may be possible to store rational numbers using two big integer fields (or maybe three for mixed numbers like 5 1/2 which help to keep the size of the numerators down). Multiplying and dividing such number representations would be easy. Adding and subtracting are more involved but are by no means difficult. It is also beneficial to calculate greatest common factors when working with rational numbers. Overflow in the denominators might be an issue.
> 
> If routines could be written on the database side, I would be willing to give it a try. I need to learn more about databases anyway. :-)
> 
> Sent from my iPad
> 
> On Mar 12, 2015, at 4:12 PM, Sébastien de Menten <sdementen at gmail.com> wrote:
> 
>>> As for numbers, I looked into that rather extensively last summer. No
>>> existing decimal library was able to handle the range of fractions
>>> necessary for bitcoin to Indonesian Rupiyah while adequately controlling
>>> rounding and providing adequate performance, so I decided to stay with our
>>> rational numbers. It does have the disadvantage of moving most calculation
>>> out of the database, so we may have to revisit it later.
>>>
>>> Hello John,
>>
>> I would be interested to have a bit more details on your findings in this
>> respect. If you look at a library like libmpdec (
>> http://www.bytereef.org/mpdecimal/doc/libmpdec/index.html), does it lack
>> features or performance or both ? And with regard to performance, in which
>> cases are the arithmetic operations the bottleneck in GnuCash ? Is it in
>> some report calculation ? In the (de)serialisation ?
>> _______________________________________________
>> gnucash-user mailing list
>> gnucash-user at gnucash.org
>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> -----
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
> 
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
> 



More information about the gnucash-user mailing list