![AI in elementary and middle schools [NAESP]](https://dangerouslyirrelevant.org/wp-content/uploads/2025/01/NAESP-Logo-Square-1.jpg){kind=logo}
Transaction management with one connection
We have a desktop application written in Java that communicates with a DB. We support Oracle and Postgres. For the purpose of this question, let's focus on Postgres Our app uses one connection for all transactions. The reason for that is we rely heavily on temporary tables. For example, it may store the current application's code. DB may provide different sets of data to different apps. In other words, our DB server is aware of its current client, unlike, say, a REST server. While you may suggest passing the code as a parameter, there are also more arcane and less replaceable ways temporary tables are used Sometimes, we need to perform a time-consuming data fetch in a separate thread. The other thread, in the meanwhile, may also hit the DB, less expensively Our understanding is Postgres requires manual transaction management. We disable the autocommit and manually commit at the end of query execution. I believe, manual transaction management has something to do with cursors. When we manually commit a transaction (that is, call connection.commit()), the other query fails with a "cursor N does not exist" error GPT mentioned savepoints and cursors WITH HOLD, among other things (aside from redesigning our architecture to use connection pools which doesn't seem feasible at the moment) How do you suggest we resolve this issue?
We have a desktop application written in Java that communicates with a DB. We support Oracle and Postgres. For the purpose of this question, let's focus on Postgres
Our app uses one connection for all transactions. The reason for that is we rely heavily on temporary tables. For example, it may store the current application's code. DB may provide different sets of data to different apps. In other words, our DB server is aware of its current client, unlike, say, a REST server. While you may suggest passing the code as a parameter, there are also more arcane and less replaceable ways temporary tables are used
Sometimes, we need to perform a time-consuming data fetch in a separate thread. The other thread, in the meanwhile, may also hit the DB, less expensively
Our understanding is Postgres requires manual transaction management. We disable the autocommit and manually commit at the end of query execution. I believe, manual transaction management has something to do with cursors. When we manually commit a transaction (that is, call connection.commit()), the other query fails with a "cursor N does not exist" error
GPT mentioned savepoints and cursors WITH HOLD, among other things (aside from redesigning our architecture to use connection pools which doesn't seem feasible at the moment)
How do you suggest we resolve this issue?
What's Your Reaction?
