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

[John Ralls]

> On Mar 13, 2015, at 9:44 PM, Sébastien de Menten <sdementen at gmail.com> wrote:
> 
> Thank you John for these references (i already read some of them in the past but never got the full thread).
> 
> Some little points remain confused in my mind:
> - you mention that most of the time the denominator is a power of 10 (as at the end, financial institutions always round to some digit) and so the full rational type is not needed an a decimal type would be enough. But are there times where rationals are needed ? If so, would you have some examples :  intermediate calculations ?  prices ? SX parameters ?
> - you also mention rounding issues with decimals. Have you an example of this ? is it only on rounding on numbers like 1/3 (so rationals, not decimals) ? or even with only decimals you may have rounding issues ?
> - in terms of performance, do I understand correctly that using a decimal library to add to numbers like 1.34 + 100. is slower than adding the equivalent fractions 9,37 / 7 + 1000 / 10 with the integer code in gnucash ? Or is it only more performance in the case where we add 1.34 + 100. on one side and 134/100 + 10000/100 on the other side ?
> - in terms of performance, do I understand also correctly that with the "usual" books you work with, the profiling of the application runtime gives you an important part of the time spend doing "rational" arithmetic vs the rest (GUI, IO, ...) and so this point is really critical for "usual" cases ?
> 
> If so, in your long term vision (gnucash as a DB app), you would do your queries in SQL but will implement the "rational arithmetic" in SQL as well ? or would do use the classic "group by denominator" and then put the rational arithmetic logic in the C code ?

Sebastien,

Yes, most of the time with modern currencies and stock prices the denominator of a value or amount is a power of 10 until we do a division. That happens most often in calculations involving different commodities, so I suppose you could say that it's prices. SX calculations are done at least partly in Scheme and consequently in some cases they're using floats. That mostly affects interest calculations.

For rounding, the problem was worst in the lot-split assignments exercised in test-lots. Without aggressive rounding the lot-split would wind up with hundreds of splits and eventually fail with an underflow error because it could never balance the lot. Unfortunately the aggressive rounding caused unfavorable impacts in other test code. The test cases are generated with random numbers and may not always be realistic, but I'm not comfortable with constraining tests to what I might decide is "realistic" in order to get them to pass. Users are just too good at finding real-life use cases that I might not have thought was realistic.

I didn't try to understand what it is about decimal math that makes it so much slower, but it was uniformly slower in the profiling tests last summer. I think that simply having to unpack the digits and operate on them one-at-a-time without hardware support would be a sufficient reason to cause the slowdown. As I reported, I ran selected GnuCash tests (test-gnc-numeric and test-lots in src/engine/tests) in a profiler and compared their execution times; I did my best to tune the gnc-numeric implementations using profiling data to get the best performance with each library.

There are a couple of possibilities for the DB. I don't think it's feasible to provide rational arithmetic in the DB engine. Grouping by denominator is possible, but I think forcing the denominator to the account commodity's maximum denominator (100 for most currencies and common stocks, 10**8 for bitcoin, etc.) would allow addition and subtraction in queries, and that will handle most of our needs. Anything needing multiplication or division will have to be done in C++.

Regards,
John Ralls