Re: OutOfMemory

Oliver Jowett wrote:
>That sounds like a bug; last() should take you to the very last record
>of the resultset independent of the fetchsize. Can you submit a testcase
>showing this?

>(... later ...) Hold on -- doesn't last() require a scrollable
>resultset, which means you're not using a cursor behind the scenes? I
>think the driver does not throw an exception if you try to use
>last()/absolute()/etc with FETCH_FORWARD_ONLY (it should!) but instead
>just gives you the wrong results.. not great, but this is not only the
>fault of the driver :)

Yeah - at first i simply called setFetchSize(20); after creating a Statement
with
conn.createStatement(ResultSet.SCROLL_SENSITIVE,...).
If turned out to make no difference, the driver still fetched all results
into memory, which isn't easy to notice unless one uses the debugger or fill
in so many data that the OutOfMemory comes if fetchsize is not working.

So i walked through the source tree and saw that it only would use the
fetchsize by using "FETCH FORWARD ..." if the ResultSetType is set to
FETCH_FORWARD_ONLY.
This does indeed fetch only fetchsize rows but then i was stuck... so here
is my TextCase:

---
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestFetchSize {
//to be run with standard java-memory-settings: (-Xmx64m)
public static void main(String[] args) {
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
try {
//set-up:
Connection conn =
DriverManager.getConnection("jdbc:postgresql://<server>:5432/<db>",
<user>,<password>);
Statement ct=conn.createStatement();
try { ct.execute("DROP TABLE tst_fetchsize");} catch (SQLException x) {};
ct.execute("CREATE TABLE tst_fetchsize ( id bigserial primary key, a
varchar );");
Statement
cf=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet ins=cf.executeQuery("SELECT * from tst_fetchsize LIMIT 0");
String data=new String(new byte[3000000]);
data=data.replace('\0',' ');//very long and empty String
System.out.println("string-length=" +data.length());
final int count=10;
for(int i=0;i<count;i++) {
ins.moveToInsertRow();
ins.updateString(2,data);//create very large records!
ins.insertRow();
}

//test forward-only
System.out.println("test: TYPE_FORWARD_ONLY");
conn.setAutoCommit(false);//to allow cursors below
Statement
st=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
st.setFetchSize(1);
ResultSet rs=st.executeQuery("Select * from tst_fetchsize");
rs.last();//now we should be at "count"
System.out.println("getRow=" + rs.getRow() + " should be=" + count);
rs.beforeFirst();
while(rs.next()) System.out.println("this is row " + rs.getString(1));
System.out.println("now backward:");
while(rs.previous()) System.out.println("this is row " + rs.getString(1));

//test scroll*
System.out.println("test: TYPE_SCROLL_SENSITIVE, it should not give an
OutOfMemory-Exception if we fetch indeed just one record as above!");
conn.setAutoCommit(false);//to allow cursors below

st=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
st.setFetchSize(1);
rs=st.executeQuery("Select * from tst_fetchsize");
rs.last();//now we should be at "count"
System.out.println("getRow=" + rs.getRow() + " should be=" + count +" and
is, however, the driver already fetched all rows here!!!");
rs.beforeFirst();
while(rs.next()) System.out.println("this is row " + rs.getString(1));
System.out.println("now backward:");
while(rs.previous()) System.out.println("this is row " + rs.getString(1));

//cleanup:
conn.setAutoCommit(true);
ct.execute("DROP TABLE tst_fetchsize");
} catch (SQLException e) {
e.printStackTrace();
}
}
}

---

>Yes -- otherwise we'd need to use a cursor WITH HOLD and manage it more
>carefully. Currently we rely on end-of-transaction closing the cursors,
>and that scheme doesn't work with autocommit on so we don't use cursors
>in that case.

Ok, understood - the database holds all the result-data of a query in memory
already.

>This is actually a tradeoff between storing the resultset on the backend
>and storing it on the java process -- WITH HOLD is not free, someone has
>to store the data once the transaction is gone. I think the backend does
>a better job of paging out results etc though.

So it is - especially with Blobs, as JDBC needs 5 to 8 times the size for
each blob because of

1. storing the whole result in a UNICODE-StreamBuf (including the bytea-part
which is only ASCII)
2. quoting of half the characters of the blob with one (') to three (\)
characters is required.

Without using fetchsize the Java-memory is soon exhausted...

Wouldn't it be possible to stream the bytea-parts at least directly into
byte[]-arrays as they come in over the socket, somehow? (The problem is here
that we don't know the size of the bytea, need to use a "ByteBuffer" which
can grow...)

>> I was also thinking about using the "SELECT ... LIMIT x OFFSET y"
instead, but
>> this might lead to unexpected side effects if multiple users are changing
>> data - the user gets only a momentary snapshot then, if the order has
changed
>> in between some records will never be seen, others twice and so on.
>>
>> Any nice idea to solve this?

>Wrap your queries in a transaction if you want transactional isolation.

Mmm, need to test this - but if that works, why use CURSORS (which work also
only within a transaction) in the driver and not the LIMIT/OFFSET instead?
In that case, whether the frontend nor the backend would have to copy data
(into memory) which will eventually never be fully read.

>Also you really want an ORDER BY if you're using LIMIT/OFFSET.

That was just an example to point out, that between multiple SELECTS
something could change the OFFSET of the next data to be fetched... but
snapshots are just snapshots and more than doing a "READ_COMMITTED" can't be
done here.