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

[John Ralls]

> On Mar 14, 2015, at 7:18 PM, Sébastien de Menten <sdementen at gmail.com> wrote:
> 
> 
> 
> On Fri, Mar 13, 2015 at 2:37 PM, John Ralls <jralls at ceridwen.us <mailto:jralls at ceridwen.us>> wrote:
> 
> 
> 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.
> 
> 
> I read in some recent threads that prices where not "central" in Gnucash in the sense they were used as exchange rate in cross-commodity transactions but that the only meaningful numbers at the end where those in the splits, rounded according to the account commodity or transaction currency.
> 
> I see prices also used in GUI (when need to convert all accounts to a single currency) and reports (when prices are required trough "price source" options).
> 
> Would it be fair to say that rounded decimal prices would not that much of an issue ?

No, because you're confusing two different flavors of prices. The flavor stored in the pricedb are indeed not central to much of anything. The actual price or exchange rate in a transaction is the ratio of the amount of a commodity in a split to its value in the transaction commodity, which isn't explicitly stored. That ratio must be exact in order for the transaction to balance in both commodities, and incorrect rounding causes real problems with the existing code. That doesn't mean that the code can't be changed to accommodate a less-precise number system, but for now we need rationals.

>  
> 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.
> 
> 
> In the FIFO lot logic to handle the splits in an commodity account, the key calculation is the balance of the lots with respect to the quantity in the splits. 
> If all splits quantities are decimals (ie power of 10 in denominator), I guess it should work flawlessly without adding any specific rounding logic. 
> However, if the quantities are expressed as rationals with any possible denominators (including large primes), there is a need for specific rounding logic to avoid overflowing both the numerator and denominators. For a concrete example:
> q1=Fraction(1, 22801763489)
> q2=Fraction(1, 22801763513)
> q3=Fraction(1, 22801763527)
> q4=Fraction(1, 22801763531)
> q1+q2-q3-q4=Fraction(29115543492447662469968, 270317242502817414843191354525532448010309)
> 
> It let me wonder if the question is here is not simply "should rationals that are not decimals be allowed anywhere in gnucash BUT in prices ?".
> Prices are a bit of an exception (with respect to value or quantities) as they express a ratio between a value (or quantity) and another value (or quantity). 
> So if all quantities and values are constrained to decimals and prices are expressed as ratios between two decimals (which there looks like the current fraction), we are left with rounding issues only when using prices.
> And in fact, it is what is more or less proposed in gnucash by default (scu in accounts and fraction in commodity are by default power of 10).
> 
> If in the test-lots, denominators for quantities are constrianed to be power of 10, would there still be performance or rounding issues ?

I *think* I've successfully constrained the denominators in test-lots to powers of 10, and the rounding issues remain. The problem is that even if the denominators of each commodity amount are powers of 10, as soon as you introduce another commodity the denominator of the price/exchange rate can no longer be so constrained.

As for performance, one more time: All manipulations of BCD on PC architectures (ARM, Intel, and Power) are purely in software: There is no hardware support for BCD. That gives integer math a significant performance advantage without doing anything else.

> 
> 
> 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.
> 
> 
> In this "micro-benchmark", the delta in performance you observed was significant. But I was wondering if, for a normal gnucash work session, the time spent in rational calculations was significant or not ? I would guess that IO (save to sqlite backend) and GUI refreshes takes more time than these operations but I have really no clue. If overall, the calculations are just 1% of the time spent in gnucash, even with a x4 deterioration, it would not make a difference for the user (an gnucash is quite snappy overall). 

Maybe. It would depend upon what's in a book and what operations are done in a session. It would take a lot of work to develop a good set of benchmarks to evaluate that. Since you're the one beating the horse to death, perhaps you can spend some time working on it.

>  
> 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++.
> 
> 
> Currently, there is no enforcement that a split has always the same value denominator as all splits for the transaction it relates nor has the same quantity denominator as all splits for the account it relates. This mandates a two step query to do calculate balances (first aggregation on same denominator and then combination of these sums).
> Would there be a plan to enforce these "common" denominator to simplify queries ? or would this be too much of a constraint ?

I meant a common denominator within an account. I don't think that a common denominator is meaningful in a multi-commodity transaction: For example, US Dollars, Euros, and Pounds Sterling all have a common denominator of 100, but a transaction between any two of them will involve a denominator that is not 100.

Regards,
John Ralls