Re: Help: OutOfMemoryError

Since you are doing a select * without any where clause, postgres is
going to return the entire table back to the client all at once. The
client then needs to store the entire result in memory, thus you will
need enough memory to hold the entire table in memory.

A better way to do this in postgres is to use a cursor. (see the
declare cursor, and fetch SQL statements). Using a cursor you can fetch
a specific number of rows at a time, instead of all or nothing. This
will require a lot less memory on the client.

thanks,
--Barry

Dongsheng Song wrote:

> When I use export a lager table, the java application throws exception 'OutOfMemoryError',
> How can I do ? this is the source:
>
> /*
> export table
> */
>
> import java.io.*;
> import java.sql.*;
> import java.util.*;
>
> public class ExpTbl_MS {
>
> private static String db_res[][] = {
> { "com.ashna.jturbo.driver.Driver", "jdbc:JTurbo://egcs:1433/egcs/charset=GBK" },
> { "net.avenir.jdbc2.Driver", "jdbc:AvenirDriver://172.16.1.156:1433/fpb" },
> { "org.gjt.mm.mysql.Driver", "jdbc:mysql://egcs:3306/fpb?useUnicode=true&characterEncoding=gb2312&autoReconnect=true" },
> { "org.postgresql.Driver", "jdbc:postgresql://orchis:5432/fpb" },
> { "oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@202.100.100.201:1521:ORA" },
> { "COM.ibm.db2.jdbc.net.DB2Driver", "jdbc:db2://egcs:6789/fpb" },
> { "sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:sql" },
> { "com.ms.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:sql" },
> };
>
>
> public static void main(String[] args) throws Exception {
>
> int i = 0, j;
> String db_drv = "com.ashna.jturbo.driver.Driver";
> String db_url = "jdbc:JTurbo://172.16.19.2:1433/agriec/charset=GBK";
> String db_usr = "sa";
> String db_pwd = "zm";
> String db_exp = "ExpTbl_MS.dmp";
>
> while(i < args.length) {
> if(args[i].startsWith("db_drv=")) {
> db_drv = args[i].substring(7);
> } else if(args[i].startsWith("db_url=")) {
> db_url = args[i].substring(7);
> } else if(args[i].startsWith("db_usr=")) {
> db_usr = args[i].substring(7);
> } else if(args[i].startsWith("db_pwd=")) {
> db_pwd = args[i].substring(7);
> } else if(args[i].startsWith("db_exp=")) {
> db_exp = args[i].substring(7);
> } else {
> break;
> }
> i++;
> }
>
> Class.forName(db_drv);
>
> Connection db_cn = DriverManager.getConnection(db_url, db_usr, db_pwd);
>
> //Statement db_st = db_cn.createStatement();
> Statement db_st = db_cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
> for(j = 0; i < args.length; i++) {
> try {
> System.out.print("starting export table '" + args[i] + "' ");
> export(db_st, args[i], (db_exp + "." + j));
> System.out.println(" OK");
> } catch(Exception se) {
> System.out.println(" Fail(" + se.getMessage() + ")");
> }
> System.out.println("\n");
> }
>
> db_st.close();
> db_cn.close();
> }
>
> public static void export(Statement db_st, String tableName, String of)
> throws SQLException, IOException {
>
> ObjectOutputStream os = new ObjectOutputStream(new BufferedOutputStream(new FileOutputStream(of), 512 * 1024));
> ResultSet db_rs = db_st.executeQuery("select * from " + tableName);
> ResultSetMetaData db_rsmd = db_rs.getMetaData();
> int i, j, n = db_rsmd.getColumnCount();
> Object obj;
>
> os.writeInt(0);
> os.writeObject(tableName);
> os.writeInt(n);
> for(i = 1; i <= n; i++) {
> os.writeObject(db_rsmd.getColumnName(i));
> os.writeObject(db_rsmd.getColumnTypeName(i));
> os.writeInt(db_rsmd.getColumnDisplaySize(i));
> os.writeInt(db_rsmd.getPrecision(i));
> os.writeInt(db_rsmd.getScale(i));
> os.writeInt(db_rsmd.isNullable(i));
> }
>
> for(j = 0; db_rs.next(); j++) {
> for(i = 1; i <= n; i++) {
> obj = db_rs.getObject(i);
> if(obj == null || db_rs.wasNull()) {
> os.writeObject(null);
> } else if(obj instanceof byte[]) {
> os.writeObject(obj);
> } else if(obj instanceof java.sql.Timestamp ||
> obj instanceof java.sql.Date ||
> obj instanceof java.sql.Time
> ) {
> os.writeObject(new java.lang.Long(((java.util.Date)obj).getTime()));
> } else if(obj instanceof java.sql.Blob) {
> Blob blob = (Blob)obj;
> os.writeObject(blob.getBytes(1L, (int)blob.length()));
> blob = null;
> } else if(obj instanceof java.sql.Clob) {
> Clob clob = (Clob)obj;
> os.writeObject(clob.getSubString(1L, (int)clob.length()));
> clob = null;
> } else if(obj instanceof java.io.InputStream) {
> System.err.print("Unknown, ");
> } else {
> os.writeObject(obj.toString());
> }
> obj = null;
> }
> if(j % 1000 == 0) {
> System.out.print('.');
> System.gc();
> System.runFinalization();
> System.gc();
> os.flush();
> }
> }
> }
> }
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>