Using an externally hosted postGreSQL as a gnu cash backend?

[Russell Mercer]

I recently flushed my gnucash list archives.  The main thrust related to
data integrity with the db backend is listed at this link:

http://gnucash.org/features.phtml

Look under Data storage and exchange features, and "Experimental database
support".  I'm sure others on the list can provide more detail as to where
these corner cases are usually found.

As for the database transactions.  I was mistaken with my flat file
comment.  The gnucash data is broken up into tables.  The structure is
available at:  http://wiki.gnucash.org/wiki/SQL
All relation information is in the C code.  Tables are queried into
memory.  All transaction operations are performed on memory tables, then
tables are written back to the database.

This thread is probably the most recent discussion on the issue:
http://lists.gnucash.org/pipermail/gnucash-user/2011-February/038517.html
It contains some of the above statements plus a bit more detail.

Thanks,
Russell


On Mon, Apr 11, 2016 at 3:39 PM, Michael Wagner <mikepwagner at mikepwagner.net
> wrote:

>
>
> On Mon, Apr 11, 2016 at 6:12 PM, Russell Mercer <rmercer206 at gmail.com>
> wrote:
>
>> Mike,
>>
>> I think there are a couple of things at play here.
>>
>> 1.  Using a database backend as opposed to the .xml file:  There are
>> noted issues with data consistency when using any backend storage aside
>> from the .xml.  The issues may not be major, and may only apply in certain
>> areas like business functions, but that has not been quantified.  In short,
>> there are known data integrity issues, but their scope and severity are not
>> known as they pertain to any or all database backends.  That is one primary
>> reason other people on the list have recommended against using it.
>>
>
> Is there a nickel tour of the data consistency issues? I did a quick web
> search and didn't find anything.
>
>
>>
>> 2.  Postgres data integrity, ACID support, etc.  I use Postgres on a
>> daily basis for my work, so I completely understand and agree that it is a
>> stellar RDBMS.  I do not, however, use it for Gnucash.  The primary reason,
>> is as mentioned in item 1.  There are unresolved data integrity issues.
>> The secondary reason is that at this time, Gnucash has not been modified to
>> take advantage of any database functionality.  To your example, a partial
>> transaction could not happen in Gnucash, but for a different reason..  A
>> transaction between accounts in Gnucash does not involve a transaction
>> between 2 tables in Postgres.  This is because the data is essentially
>> stored in a flat file in Postgres.  It is loaded completely into memory and
>> all operations are done in memory with the results written back to the
>> database.
>> So, yes, while in theory, the RDBMS functionality of Postgres should
>> provide far superior data protection than the .xml file, in practice, it is
>> no better.
>>
>
> Sorry, I hope my explanation wasn't too tedious.
>
> So gnucash doesn't update the PostGres DB using transaction semantics?
> That's interesting - not doubting you, that just seems odd.
>
>
>>
>> Hope this helps,
>> Russell
>>
>
> Thanks,
>
> Mike
>
> --
> “The bassoon is one of my favorite instruments. It has a medieval aroma,
> like the days when everything used to sound like that. Some people crave
> baseball...I find this unfathomable, but I can easily understand why a
> person could get excited about playing the bassoon.” - Frank Zappa
>