TL;DR:
I need a database procedure that runs entirely independent of the calling backend service and commits when finished.
I have a database procedure that might run up to several hours
create procedure copy_stuff(...)
begin
...
commit; <-- This is the crucial line
end
This function is called asynchronously by a spring boot backend service when a REST endpoint is executed.
Using
@Query("CALL copy_stuff(...)", nativeQuery = true)
@Modifying
fun copyVersion(...): Int
It is explicitly wanted and really important that this procedure keeps running when the backend service shuts down! This is why I need to commit at the end of procedure
However since JPA handles transaction I get the error ERROR: invalid transaction termination
When removing the commit
statement in the database procedure everything works as intended.
HOWEVER when starting the procedure and shutting down the backend service, the procedure keeps running but since there is no commit statement and no transaction management by JPA, the procedure terminates but nothing gets written to the database.
To wrap it up:
- To call the procedure from JPA I need a transaction but then – without the transaction – the procedure does not work after shutting down the backend service
- To make the procedure work entirely I need the commit statement but then JPA does not let me finish the procedure because it wants the commit to be executed by itself
So how do I call the database procedure so it runs entirely independent of JPA?