Re: defaultRowFetchSize and no known snapshots error - Mailing list pgsql-jdbc

Hi everyone

I messed with this exception: PSQLException: ERROR: no known snapshots
I found that it arise when:
* defaultRowFetchSize connection parameter is set to a positive value (value 100 in my case) and
* a ref_cursor is read and it has more rows than the limit and
* the cursor contains toasted data (bytea in my case)
When the record no 101 is to be fetched, the exception is trown.

Note that CallableStatement.setFetchSize and ResultSet.setFetchSize works as expected.

I suggest at least adding a note about this limitation in the defaultRowFetchSize connection parameter description.
Thank you.

Best regards
David


Simple test case:

SQL:
create table test_blob(content bytea);
-- generate 101 rows with 4096 bytes:
insert into test_blob
select (select decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256) - 1), 2, '0'), ''), 'hex') FROM generate_series(1, 4096))
from generate_series(1, 101);

CREATE OR REPLACE FUNCTION test_blob(p_cur OUT REFCURSOR) AS $body$
BEGIN
  OPEN p_cur FOR SELECT content FROM test_blob;
END;
$body$ LANGUAGE plpgsql STABLE;

Java:
  public static void main(String[] args) throws SQLException {
    String url = "jdbc:postgresql://localhost/postgres?defaultRowFetchSize=100";
    Connection conn = DriverManager.getConnection(url, "user", "password");
    conn.setAutoCommit(false);

    int cnt = 0;
    try (CallableStatement stmt = conn.prepareCall("{? = call test_blob()}")) {
      stmt.registerOutParameter(1, Types.REF_CURSOR);
      stmt.execute();
      ResultSet rs = (ResultSet) stmt.getObject(1);
      while (rs.next())
        cnt++;
    }
    finally {
      System.out.println("records read: " + cnt);
    }
    conn.close();
  }

Output:
records read: 100
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: no known snapshots
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
        at org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:2480)
        at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1958)
        at TestBlob.main(TestBlob.java:19)