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)