Обсуждение: 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.
2009/7/21 Shawn Heisey <pgsql@elyograg.org>
Is not it easier to use a delegating driver that does required actions on *Statement creation? You won't have to get into Postgresql JDBC driver and will have no problems with any driver updates.
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.
Is not it easier to use a delegating driver that does required actions on *Statement creation? You won't have to get into Postgresql JDBC driver and will have no problems with any driver updates.
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
2009/7/21 Shawn Heisey <pgsql@elyograg.org>
To the person with the Cyrillic name that also responded ... how much effort would be involved in writing the delegating driver you described?
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.