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

[Sébastien de Menten]

On Fri, Mar 13, 2015 at 2:37 PM, John Ralls <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 ?


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


> 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 ?

regards,

sebastien