Using Postgresql 10.19 with KMyMoney 5.08

[Jack]

Hello Dave,

First, please understand that using a SQL back end for KMyMoney will  
NOT let two users access the database at the same time, or at least no  
without almost certain loss of date.  The reason is that KMM does not  
use the database in an active way.  Actions are just saved in memory,  
not committed to the database in "real time."  KMM slurps in the entire  
database when it starts (equivalent to reading a .kmy or .xml file) and  
when you execute a Save, it saves all the in memory data out to the  
database.  There is no way to allow concurrent access.  This might  
conceivably change in the future, but that's the way it is now.

I'm not sure about your specific errors below, but I get similar when I  
try to save to Postgres.  I do believe posgres is in general case  
sensitive, but you need to be careful about quoting, although I can't  
tell you exactly where or how.

 From the little testing I just tried, save as postgres fails with no  
database found if there isn't one, but it does create one.  If there is  
one, it seems to complain it can't create one since it already exists.   
I was able to manually create on and then copy the sql from the  
Tools/Create ... to a command line, but KMM then complained that the  
existing data needed to be deleted before saving the new data.

I don't believe the SQL back end is used very often, and it's quite  
possible that the last commit to fix something there managed to break  
something else.  I think this is going to take some hunting through the  
code to find the problem.

Please confirm you are trying to save data currently stored as kmy or  
xml to a database (as opposed to creating a new one.)  I'm not sure it  
matters, but I don't know why it would ask about currency with an  
existing set of data.

I see you are using 5.0.8 with Linux Mint.  That version is two years  
old, and I'm pretty sure some fixes have been applied since then in  
this area.  Can you find a newer version, compile your own?  I might  
also suggest trying an appimage (see  
https://binary-factory.kde.org/view/AppImage/job/KMyMoney_Release_appimage/).   
There have been some problems with recent changes to how they are  
built, but it might be worth trying one anyway, just to see if it still  
behaves the same way, actually works, or has a different failure.

Jack

On 2022.01.20 13:15, Dave Kelly via KMyMoney wrote:
> Hi All,
> 
> I have just started using KMyMoney and I am really happy with it. The  
> only thing is, I would like to use it with Postgresql. Hopefully, the  
> database will enable both me and my wife to enter transactions at the  
> same time.
See below for why this will not do what you want.
> 
> However, when I try to save the data out to Postgresql, I get errors.  
> The first method I used was File|Save as and select SQL and put in  
> the database parameters. It is a server on my local network. The  
> error it returns is:
> 
> Error in function int MyMoneyStorageSql::open(const QUrl&, int, bool)  
> : opening new database
> Driver = QPSQL, Host = 192.168.0.8, User = dave, Database = KMyMoney
> Driver Error: QPSQL: Unable to connect
> Database Error No -1: connection to server at "192.168.0.8", port  
> 5432 failed: FATAL:  database "KMyMoney" does not exist
> 
> Text: connection to server at "192.168.0.8", port 5432 failed:  
> FATAL:  database "KMyMoney" does not exist
> QPSQL: Unable to connect
> Error type 1
> Executed:
> Query error No -1:
> Error type 0
> 
> When I take a look at the database server, a kmymoney database has  
> been created but there are no tables. I thought it had to do with the  
> capitalisation because the error message and default is mixed case.
> 
> So, I dropped the KMyMoney database in Postgresql and tried to save  
> as again specifying an all lowercase database name “money”. I get the  
> following error:
> 
> Cannot save to current database: Error in function void  
> MyMoneyStorageSqlPrivate::deleteKeyValuePairs(const QString&, const  
> QVariantList&) : deleting kvp for STORAGE
> Driver = QPSQL, Host = 192.168.0.8, User = dave, Database = money
> Driver Error:
> Database Error No -1:
> Text:
> Error type 0
> Executed: DELETE FROM kmmKeyValuePairs WHERE kvpType = ? AND kvpId =  
> ?;
> Query error No 42601: ERROR:  syntax error at or near "("
> LINE 1: EXECUTE  ('STORAGE', '')
>                 ^
> (42601) QPSQL: Unable to create query
> Error type 2  
> /build/kmymoney-2XFvAS/kmymoney-5.0.8/kmymoney/plugins/sql/mymoneystoragesql_p.h:1927
> 
> 
> This time, a series of tables starting with kmm are created despite  
> the error. So I decided to try and open it. First I get prompted to  
> select a currency so I tried GBP. As soon as I accept this dialog  
> box, I get:
> 
> “Uncaught error. Please report the details to the developers”
> 
> Here are the details:
> 
> Unknown account id 'AStd::Asset'  
> /build/kmymoney-2XFvAS/kmymoney-5.0.8/kmymoney/mymoney/storage/mymoneystoragemgr.cpp:142
> 
> KMyMoney then closes.
> 
> Ok, I thought I would try
> 
> Tools|Generate database SQL and create the database by hand. Copied  
> the SQL and pasted into an SQL query and it ran successfully. Cool!
> 
> When I tried to open the database in KMyMoney, I get the currency  
> prompt again and there is no data.  Do I need to Export from the xml  
> version to import into the Postgres version? I tried exporting one  
> account but it doesn’t make sense how to import it.
> 
> I am using version 5.0.8 from the repo on Linux Mint 20.2 Cinnamon. I  
> am using "PostgreSQL 10.19 (Ubuntu 10.19-0ubuntu0.18.04.1) on  
> x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04)  
> 7.5.0, 64-bit"
> 
> Thanks!!!
I'm not going to address your errors here, but using a SQL back end for  
KMyMoney will NOT let two users access the database at the same time,  
or at least no without almost certain loss of date.  The reason is that  
KMM does not use the database in an active way.  It slurps in the  
entire database when it starts (equivalent to reading a .kmy or .xml  
file) and when you execute a Save, it saves all the in memory data out  
to the database.  There is no way to allow concurrent access.  This  
might conceivably change in the future, but that's the way it is now.