Memory leak in long running transaction

[laurentthiebaudezm]

Hi,

Using pgsql & suspended transactions in dsl mode, we have a long running transaction with a loop, e.g.

  transaction {
   var doContinue = true
    while (doContinue) {
       val toProcess = getDbElementsToProcess()
       processElements(toProcess)
       if (toProcess.isEmpty()) doContinue = false
    }
  }

If we have a huge number of elements to process (> 100 000) and the transactions lasts few minutes, we can eventually have an Out Of Memory. Looking in VisualVM, we see this is caused by multiple PreparedStatements that are never garbage collected.

Yet if we open a new transaction on each loop run, we don't have troubles anymore. e.g.

   var doContinue = true
    while (doContinue) {
       transaction {
          val toProcess = getDbElementsToProcess()
         processElements(toProcess)
         if (toProcess.isEmpty()) doContinue = false
       }
    }

From another post, we understood that closing the transaction does close all prepared statements and free the memory.

However we'd like to avoid having to open a new transaction on each loop run. Is there any way to close prepared statements properly, optionally manually?