Is it possible to call a stored procedure asynchronously using Hibernate? The used connection should be released after triggering the stored procedure while the query continues to run in the background on PostgreSQL. The stored procedure should then write a result in a dedicated table from which we can collect it later. Main motive is to prevent connection exhaustion of c3p0 connection pool.
Not sure if this is possible. Basically, Hibernate allows developers to avoid JDBC usage, but internally uses it extensively, and as far as I know, there is no JDBC support for asynchronous stored procedures call.Moreover, I wonder how using async calls may help you to prevent connection pool exhaustion. Are there so many calls that all connections in the pool are used ? Try increment conn pool size first. Or is this stored procedure so long lasting you are getting into troubles (JDBC timeouts, for example)? In this case I would suggest you to review your application and have a look at any batch processing framework (Spring batch for example, but many others are out there).Naively, you can submit a batch job that calls your stored and notify your application via jms when results are ready.
posted 2 years ago
Actually I want to delete millions of record. But. I have set c3p0 unreturnedconnection timeout to 15 secnd. Which is very less . so deletion of records is failing everytime. That's why I wanted To use stored procedure and after Triggering the stored procedure I wanted to close the connection so that quesry continue to run on postgresql database side and after completion it should write result to any table and. Later. I can collect data from that table. Is it possible?
It's difficult if not impossible to suggest anything which makes sense without knowing in deep your working scenario. Deleting millions of records may be actually a time consuming operation, so if you don't increase timeout of your connection, the call may fail.
This is why using a batch process may be a good idea. Another options is to use scheduled job on PostgreSQL.
You may schedule your stored procedure to run every x minutes (or hours), and make it really process data only if some column in a "trigger job table" retains a given value. For example, if your stored reads a '0' value, simply returs, if reads '1' starts deleting and doing its job.
To make your stored run in background, your application may simply update to '1' the 'flag' column on the trigger job table.
The problem in this scenario is how to notify your application that data have been processed, supposing that this notification should happen automatically, and there's nothing like, let's say, a web page where an user go and gets results (if any).
nishu midha wrote:That's why I wanted To use stored procedure and after Triggering the stored procedure I wanted to close the connection so that quesry continue to run on postgresql database side and after completion it should write result to any table and. Later. I can collect data from that table. Is it possible?
Is there any possibility to execute this as a batch process?
We have done something similar: each night +- 1 million of records are inserted into new tables and then several stored procedures are executed to perform a bunch of business related tasks and conversions so our application will be able to work with this data. It takes 50 minutes to go through the whole process of importing the data and running the stored procedures. It's processed in batch and runs each night.