Thread: Forcing cursor behavior

I have been scouring everything I can find trying to get my problem
solved.  I've turned up a number of discussions, but nothing so far that
does what I want.

I've got a java application, to which I cannot get source code, that
needs to process a query with two million rows in it, out of 35 million
total.  Currently we use it with another database, and we are trying to
migrate to Postgres.  I've got it talking to the database via your
driver, but it runs out of memory because the driver works as designed
and tries to return the entire query at once.  If I could modify the
program, then I could just turn off autocommit, set the fetchsize, and
everything would be well, but that's not an option.

I want to modify the driver to force the cursor behavior with a
fetchsize of 10,000 rows.  The program does not modify the database, so
I don't need to worry about autocommit.  I've tried changing the code in
every place that I can find, but haven't had any luck.  It still
compiles and works, but exactly the same way it did before.  I have some
experience with code, but my work experience is admin, not programming.
I have no specific experience with Java.

Would it be possible to patch the jdbc driver so that it will internally
turn autcommit off, set the fetchsize to 10,000, and ignore any attempt
by the application to change these parameters?  Could someone create
such a patch for me? I am using java 1.5, so the driver compiles as the
3g version.

We have a support contract on the application in question, but we've
already been told that the development of Postgres compatibility would
not be covered by that.  They want at least three weeks of calendar time
to certify it, and an amount of money that has not yet been specified,
neither of which we can afford.

I'm not sure what you are referring to you when you say another tool.
Are you talking about a replacement for the application?  That would not
be easy.  It's a highly specialized product.  We've got plans to try and
develop our own, but that will be at least a few months in the making.

To the person with the Cyrillic name that also responded ... how much
effort would be involved in writing the delegating driver you described?

dmp wrote:
> Hello,
>    I'm not sure why you seem intent on pursuing the course you have
> taken for patching
> the JDBC driver and perhaps someone else from the forum could address
> your need in
> this area for a solution. I would have not ruled out first the
> possible alternative solution
> of finding another tool that would access both your existing database
> and PostgreSQL
> to accomplish your objective. Second, there are decompilers out there
> that might allow
> you access to the existing source Java application so you could patch
> the code to accomplish
> your goal. Maybe who ever created the application might also be able
> to help.
>
> danap.
>
>> I have been scouring everything I can find trying to get my problem
>> solved.  I've turned up a number of discussions, but nothing so far
>> that does what I want.

Shawn Heisey wrote:

> To the person with the Cyrillic name that also responded ... how much
> effort would be involved in writing the delegating driver you described?

http://onjava.com/pub/a/onjava/2001/12/05/optimization.html may be a
useful starting point for this.

You'd basically want a set of wrappers as described there, plus a Driver
or Datasource implementation (depending on what your app uses) that
handled building a wrapper Connection around the real connection
obtained from the PostgreSQL driver. Then your wrapper implementations
can mangle autocommit and fetchsize as you see fit (and presumably not
delegate setAutoCommit(true) calls from the app down to the driver either)

-O

It depends on how you configure which driver is used - with DataSource or DriverManager URL. Simply wrapping a Connection would be few hundred lines of similar code that can be done in few days (few hours for "fast prototype") - I've done similar things for running tests on embedded Java DB instead of postgres used in production.
Registering new prefix in DriverManager (e.g. "jdbc:postgrespatched:url") may take some more time - one need to look into JDBC SPI.

Best regards, Vitalii Tymchyshyn.