[odb-users] ERROR: prepared statement already exists

[Andrea Ranfone]

Hi everyone,

I'm using odb version 2.4.0 with PostgreSQL 12 database and I stepped on 
a strange situation when retrying a transaction after a deadlock exception.

When a recoverable exception occurs and the transaction is rolled back, 
it seems that the deallocation of the prepared statement created in the 
failed transaction fails with the following error:

ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
STATEMENT:  deallocate "query_class_name"

So when I try to run a new transaction to retry the failed transaction I 
get the following error:

ERROR:  prepared statement "query_class_name" already exists


Why the prepared statement is not being rolled-back with the entire 
transaction? Is there something I'm missing about odb::recoverable 
exception handling?


The following is the code snippet I'm using to handle a recoverable 
exception:

template <typename F>

void callFunctionWithTransactionRetry(F &&function, const 
odb::connection_ptr &connectionPtr) {
   auto transactionMaxRetryCount =5;
   for (unsigned short iteration(0);; iteration++) {
     try {
       odb::transaction t(connectionPtr->begin());
       function();
       t.commit();
       break;
     } catch (odb::recoverable &e) {
       if (iteration >= transactionMaxRetryCount) {
         throw_with_trace(MaxRetryExceededException(
             "Max retry exceeded performing database operation. Error: " 
+ std::string(e.what())));
       } else {
         Logger::get().log_debug(
             "A recoverable error occurred performing db operation: " + 
std::string(e.what()) + ". Trying again.");
         continue;
       }
     }
   }

}


Regards,

Andrea Ranfone