Failure to save as postgresql database

[Jack]

Update on status, quoting below from different messages.

On 2021.01.05 08:22, Thomas Baumgart wrote:
> On Dienstag, 5. Januar 2021 01:58:33 CET Jack wrote:
....
> This happens in qMakePreparedStmtId(). Since the stmtId in the  
> failing case is empty, it must have been cleared or never set. The  
> member variable is set when a call to QPSQLResult::prepare() returns  
> true. If it returns false, the stmtId is cleared.
So this does seem to be the case.  More below, but yes, the prepare is  
failing, but I still can't figure out why, despite lots of stepping  
with gdb.

....
> So it seems to me, that exec is called in the wrong spot. To track  
> this down (After all F1 identifies 241 iterations) I would derive a  
> new result object from QPSQLResult or QSqlResult and override  
> prepare() and exec() to keep track of the calls and their return  
> code. Then use this object to add specific debugging code/output.
That many iterations was only due to multiple attempts within the same  
session.
I had been misreadeing your suggestion, as far as I can tell, the basic  
code paths are traversed several times successfully before something  
going wrong, so perhaps adding debugging code with some sort of  
conditional execution would be better - but I'm still at a bit of a  
loss to see where to add stuff.

> I think I have done something similar for MySQL to track down a DB  
> problem in the past. Yes, it's in
>   kmymoney/kmymoney/plugins/sql/kmmsqlquery.[h,cpp]
I was on the 5.1 branch, and wondered why I wasn't finding this - but  
finally realized I needed to be in master branch.
> 
> and can be turned on using ENABLE_SQLTRACER. You may use it to add a  
> KMMSqlResult object. The #define at the end of kmmsqlquery.h and the  
> #undef at the beginning of kmmsqlquery.cpp do the trick of using the  
> identical (KMyMoney) source code and switch the stuff with cmake or  
> ccmake (ENABLE_SQLTRACER).
I also turned on QT_DEBUG_SQL and recomipled qtsql, which gave me a  
little further debugging output.  I may now almost understand how this  
works, but haven't yet figured out how to expand on it.

[from later email]
>> But the whole thing works for sqlite and MySql/Mariadb, so I'm  
>> assuming  the problem is likely in postgres specifics of qsql.
> or in the way we use it. I still have the feeling that one of the  
> calls to prepare() fails and we don't take notice and then calling  
> exec() causes the problem. Not sure though. Have not looked at our  
> code lately in that area.
Yes, the prepare is what fails, even though it is preparing the exact  
same statement as a few times earlier in the process.  My efforts now  
are trying to figure out why the prepare fails.  Eventually we should  
probably put a try/catch around the prepare's as well as the exec's.

>> I think the only reason that number is so high is that I made a  
>> whole  lot of attempts within that ddd/gdb session.   I just started  
>> a new  session, and the first iteration I saw was _A, so I might try  
>> putting a  watch on that value, if it is something gdb can do.
> Yes, GDB should be able to do that.
I haven't figured out where to do that - it seems that particular value  
is part of a result, which is created for each new query.  Anyway, the  
relevant part of the output is below, with some comments in []

-------------------
[this is starting about line 333 of .../plugins/sql/mymoneystorage.cpp,  
calling d->writeOnlineJobs()]

Created 79
Executed query (0ms, 1 results, 1 affected): "SELECT count(*) FROM  
kmmOnlineJobs"
79 executed "SELECT count(*) FROM kmmOnlineJobs"
Executed query (0ms, -1 results, 0 affected): "SELECT count(*) FROM  
kmmSepaOrders"
79 executed "SELECT count(*) FROM kmmSepaOrders"
Executed query (0ms, -1 results, 0 affected): "SELECT count(*) FROM  
kmmNationalAccountNumber"
79 executed "SELECT count(*) FROM kmmNationalAccountNumber"
Destroyed 79

[and finally calling d->writeFileInfo(), which calls  
deleteKeyValuePairs]
Created 80

  QSqlQuery::prepare: DELETE FROM kmmKeyValuePairs WHERE kvpType =  
:kvpType AND kvpId = :kvpId;
Error in function void  
MyMoneyStorageSqlPrivate::deleteKeyValuePairs(const QString&, const  
QVariantList&) : preparing statement to delete kvp for STORAGE
Driver = QPSQL, Host = localhost, User = jack, Database = kmm
Driver Error:
Database Error No -1:
Text:
Error type 0
Executed: DELETE FROM kmmKeyValuePairs WHERE kvpType = ? AND kvpId = ?;
Query error No 0: ERROR:  current transaction is aborted, commands  
ignored until end of transaction block
(25P02) QPSQL: Unable to prepare statement
Error type 2
void MyMoneyStorageSql::cancelCommitUnit(const QString&) - void  
MyMoneyStorageSqlPrivate::deleteKeyValuePairs(const QString&, const  
QVariantList&) s/be bool MyMoneyStorageSql::writeFile()
Destroyed 80
-------------------
Considering there is no code between "QSqlQuery query(*q);" and "if  
(!query.prepare("DELETE FROM kmmKeyValuePairs WHERE kvpType = :kvpType  
AND kvpId = :kvpId;"))" I don't know where or why the transaction gets  
aborted.  I've traced this into the bowels of qtsql  
..../src/plugins/sqldrivers/psql/qsql_psql.cpp, but I still can't  
figure out exactly what is failing.  However, at about the point of the  
failure, there is a local result which is optimized out, so I think my  
next step is to recompile qtsql with less optimization.

Jack