Hi,
Are there any plans to support holdable cursors without loading the entire data-set (which can potentially be very big)?
The database seems to support it using holdable cursors.
My usage scenario is as follow:
I have many threads that share the same connection. They use different statements and iterate their own result sets.
It is very likely for threads to process their result set concurrently. All statements are running queries only.
We are running with auto-commit false as we don't want to load the entire data-set in memory.
Every so often a thread will commit the transaction as it appears that running the transaction for too long slows down the process (DB?).
Upon commit any other thread that is currently iterating its result set using the same connection will get an exception suggesting the cursor is invalid as the transaction was committed.
We thought to solve this issue by using ResultSet.HOLD_CURSORS_OVER_COMMIT but as it appears that will trigger the driver to load the whole result set in memory (similar to autoCommit true).
Any suggestions?
Also how effective is connection multiplexing and processing/reading from multiple result set in parallel?
BTW, we noticed that all types are returned to the driver as strings and then converted to the specific type upon request (e.g. getInt). Any reason for that?
Some information:
driver: postgresql-8.2-506.jdbc3.jar (PostgreSQL 8.2 JDBC3 with SSL (build 506))
server: 8.2.4
OS: Linux XXX 2.6.16.21-0.8-smp #1 SMP Mon Jul 3 18:25:39 UTC 2006 x86_64 x86_64 x86_64 GNU/Linux
Thanks,