GnuCash PostgreSQL support

[John Ralls]

On Jan 27, 2012, at 7:48 AM, Tarlika Elisabeth Schmitz wrote:

> On Fri, 27 Jan 2012 09:57:56 -0500
> Derek Atkins <warlord at MIT.EDU> wrote:
> 
>> There are a LOT of data loss issues in
>> the SQL backend that have been fixed in later releases.  You do NOT
>> want to use 2.4.4 with SQL.  You want at least 2.4.9 if not the (just
>> released) 2.4.10.  And even there you might still find some data loss
>> issues with SQL.
> 
> Thanks for the warning, Derek.
> 
> In other words, GnuCash with SQL is not stable for real-world use at
> the moment.
> 
> I had so much been looking forward to moving to SQL for three reasons:
> 
> 1) speed (not too important) 
> 
> 2) being able to extract my own reports via SQL
> 
> 3) I want to bulk modify 1000s of transactions (I know it's not
> recommended to tamper with the data directly and I'd do a backup
> beforehand). For instance, I want to transpose Description and Notes of
> certain transactions (so they show up in the reconcile window). I have
> 8 years worth of data, 55MB xml and I'd like to restructure the way I
> record some expenses based on my notes.
> 
> I'm sure you're going to shoot me down in flames but this would either
> have to be automated or it will never be done.

The *known* data loss issues have been resolved in 2.4.9, but we don't yet have sufficient test coverage to be sure that everything is really covered. There's no speed difference, because with all backends the entire database is loaded into memory when the file or database is opened. 

Extracting reports via SQL is more difficult than it would seem at first glance because none of the "business logic" is encoded in the database (not even foreign keys). Some of the useful data are kept in nested key-value pairs which require recursive queries to extract, which isn't easy to do in pure SQL.

Your notes/description swap is certainly possible and should be safe enough -- but you could accomplish it as easily with XSLT on the XML file as with SQL. I'm not sure what you mean by "restructuring your expenses", but if it means moving splits from one account to another, that's definitely *not* safe to do outside of the Gnucash API.

Regards,
John Ralls