Program hangs after retrieving 100 rows containg CLOB

Question

I am retrieving one text column (CLOB) from a table in a "remote" H2 database (actually on a local drive, but using tcp to access it) and after retrieving the first 100 rows the program hangs on retrieving the next row of the result set. If, on the other hand, I access the same database as an embedded database, there is no problem. If I try to display the table's rows using H2's console application accessing the database using the Server (i.e. tcp) method, then I get the following error message:

IO Exception: "java.io.IOException: org.h2.message.DbException: The object is already closed [90007-164]"; 
"lob: null table: 14 id: 1" [90031-164] 90031/90031

Here is the program. If I uncomment out the call that sets the system property, the program works. I have also tried retrieving the column using a character stream or simply a call to getString, controlled by constant USE_STREAM. There is no difference in the results:

import java.sql.*;
import java.util.*;
import java.io.*;

public class Jdbc4
{
    private static final boolean USE_STREAM = false;

    public static void main(String[] args) throws Exception
    {
        //System.setProperty("h2.serverResultSetFetchSize", "50");
        Connection conn = null;
        try {
            Class.forName("org.h2.Driver").newInstance();
            conn = DriverManager.getConnection("jdbc:h2:tcp://localhost/file:C:/h2/db/test/test;IFEXISTS=TRUE", "sa", "");
            Statement stmt = conn.createStatement();
            String sql = "select select_variables from ipm_queues";
            ResultSet rs = stmt.executeQuery(sql);
            int count = 0;
            while (rs.next()) {
                ++count;
                String s;
                if (USE_STREAM) {
                    Clob clob = rs.getClob(1);
                    Reader rdr = clob.getCharacterStream();
                    char[] cbuf = new char[1024];
                    StringBuffer sb = new StringBuffer();
                    int len;
                    while ((len = rdr.read(cbuf, 0, cbuf.length)) != -1)
                        sb.append(cbuf, 0, len);
                    rdr.close();
                    s = sb.toString();
                    clob.free();
                }
                else
                    s = rs.getString(1);
                System.out.println(count + ": " + s);
            }
        }
        finally {
            if (conn != null)
                conn.close();
        }
    }
}

Here is the DDL for creating the table (you can see it was originally a MySql table):

CREATE TABLE `ipm_queues` (
    `oid` bigint NOT NULL,
    `queue_id` varchar(256) NOT NULL,
    `store_id` bigint NOT NULL,
    `creation_time` datetime NOT NULL,
    `status` bigint NOT NULL,
    `deleted` bigint NOT NULL,
    `last_mod_time` datetime NOT NULL,
    `queue_name` varchar(128),
    `select_variables` text,
    `where_clause` text,
    `from_table` varchar(128),
    `order_by` varchar(256),
    `from_associate_table` varchar(256),
    `from_view` varchar(128)
);

ALTER TABLE ipm_queues
    ADD CONSTRAINT ipm_queues_pkey PRIMARY KEY (oid);

CREATE UNIQUE INDEX ipm_queues_key_idx ON ipm_queues(queue_id, store_id);

CREATE INDEX ipm_queues_str_idx ON ipm_queues(store_id);

Answer 1

I believe I understand the the cause of the hang. I investigated the simplest case of using a h2.serverResultSetFetchSize value of 600, which is greater than the 523 rows I know that I have. As I mentioned, I can retrieve the first 3 rows (single CLOB column) okay and then I either hang on the retrieval of the 4th row or I get a "The object is already closed" exception.

It turns out that the actual string comprising the first three columns seem to be rather short in length and method getInputStream in class org.h2.value.ValueLobDb has the data already and simply returns a ByteArrayInputStream constructed on this data. The 4th row's data is still on the server side and so an actual RemoteInputStream has to be built to process fetch the data from the server-side LOB.

Here's what seems to be the problem: Class org.h2.server.TcpServerThread is caching these LOBs in in instance of a SmallLRUCache. This cache seems to be designed to maintain only the least recently referenced LOBs!!! The default size of this cache is given by system property h2.serverCachedObjects, which defaults to 64, whereas the default fetch size is 100. So even if I had not overridden the default h2.serverResultSetFetchSize property, if all of my rows had sufficiently large columns requiring cached LOBs, any fetch size > 64 would cause the LOB representing the first row to be flushed out of the cache and I would not even be able to retrieve the first row.

An LRU cache seems to be the wrong structure for holding LOBs that are in an active result set. Certainly having a default cache size that is less than the default fetch size seems less than ideal.