Re: Memory usage with Postgres JDBC - Mailing list pgsql-jdbc

Peter,

It is something I would like to see done for 7.3.  I am not sure if I
will have the time to do it though.  If you want to work on it that
would be great.  I have a couple of ideas on how to implement it such
that you can turn it on or off via the standard jdbc api.

So my ideas are that by default Statement objects work as they do today
(i.e. do not use a cursor and return the entire result set).  However if
the user of the driver calls Statement.setFetchSize() this would turn on
using 'implied cusors' and would result in fetches of fetchsize being
used.  Perhaps there should be a way to set a default value for fetch
size for all Statements so that you could turn on 'implied cursors' for
all statements instead of individually.

thanks,
--Barry


Kovács Péter wrote:

>Anyone working on "internal cursor" support? (Or would not it be a better
>way to call it "implied cursor" support?)
>
>Pete
>
>-----Original Message-----
>From: Dave Cramer [mailto:Dave@micro-automation.net]
>Sent: Friday, July 19, 2002 4:51 PM
>To: Mike R
>Cc: pgsql-jdbc@postgresql.org
>Subject: Re: [JDBC] Memory usage with Postgres JDBC
>
>
>Mike,
>
>use cursors, unfortunately you will have to do this manually, as the
>driver doesn't support them internally ( yet ).
>
>Dave
>On Fri, 2002-07-19 at 10:21, Mike R wrote:
>
>
>>Hi,
>>We are running a Java application which can run against either a Postgres
>>
>>
>or
>
>
>>Oracle database using jdbc.
>>I noticed a dramatic difference in memory usage between the two databases,
>>
>>
>
>
>
>>presumably stemming from the different jdbc drivers.
>>The simple test program below connects to a database and does a select
>>
>>
>from
>
>
>>a table containing about 40000 records (select * from mytable).  When
>>
>>
>using
>
>
>>Oracle, the memory usage peaked at about 11Mb.  With the Postgres driver
>>
>>
>it
>
>
>>peaked at 75Mb.  I suspect that the PG jdbc driver brings back the entire
>>ResultSet all at once and keeps it in memory on the client, while the
>>
>>
>Oracle
>
>
>>driver probably fetches in blocks.
>>
>>Is there any way to reduce memory usage with Postgres?
>>I know there is a setFetchSize method in the Statement interface which
>>likely was intended for this very purpose.  Unfortunately, it isn't
>>implemented by the Postgres JDBC driver (...so much for standards).
>>
>>Any help on this matter would be greatly appreciated.
>>By the way, we cannot have different SQL code for the different databases.
>>
>>
>
>
>
>>It must be standard.  So using PostgreSQL specific commands is not an
>>option.
>>
>>Cheers,
>>Mike.
>>
>>(Other info: In both cases, the application is running on Windows2000.
>>
>>
>The
>
>
>>Postgres database is on a Linux machine while Oracle is on Windows2000.)
>>
>>
>>/** Postgres Code **/
>>import java.sql.*;
>>import java.io.*;
>>
>>public class JdbcCheckupPG
>>{
>>  public static void main (String args [])
>>       throws SQLException, IOException
>>  {
>>    DriverManager.registerDriver(new org.postgresql.Driver());
>>
>>    String user;
>>    String password;
>>    String database;
>>
>>    database="jdbc:postgresql://myserver:1234/mydatabase";
>>    user    ="postgres";
>>    password="";
>>
>>    System.out.flush ();
>>
>>    Connection conn = DriverManager.getConnection (database, user,
>>password);
>>    Statement stmt = conn.createStatement ();
>>    ResultSet rset = stmt.executeQuery ("select * from mytable");
>>
>>    while (rset.next ())
>>      System.out.println (rset.getString (1));
>>
>>    rset.close();
>>    stmt.close();
>>    conn.close();
>>  }
>>}
>>
>>/** Oracle Code **/
>>import java.sql.*;
>>import java.io.*;
>>
>>public class JdbcCheckupORA
>>{
>>  public static void main (String args [])
>>       throws SQLException, IOException
>>  {
>>    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
>>
>>    String user;
>>    String password;
>>    String database;
>>
>>    database="jdbc:oracle:thin:@myserver:1521:mydatabase";
>>    user    ="test";
>>    password="test";
>>
>>    Connection conn = DriverManager.getConnection (database, user,
>>password);
>>    Statement stmt = conn.createStatement ();
>>    ResultSet rset = stmt.executeQuery ("select * from mytable");
>>
>>    while (rset.next ())
>>      System.out.println (rset.getString (1));
>>
>>    rset.close();
>>    stmt.close();
>>    conn.close();
>>  }
>>}
>>
>>
>>
>>_________________________________________________________________
>>MSN Photos is the easiest way to share and print your photos:
>>http://photos.msn.com/support/worldwide.aspx
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>>
>>
>>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
>