defaultRowFetchSize and no known snapshots error

[davecramer]

From the mailing list...

Klika David reported the following

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:https://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)

[afel]

I can confirm the bug.
We are getting this error from time to time in production.
The bad side is there is no way to dynamically overwrite fetchSize without restarting the app (neither connection.setFetchSize(0) nor resultSet.setFetchSize(0) works)

Could you guys please tell is it a Driver problem or a PostgreSQL server itself?

[davecramer]

What version of PostgreSQL is this. I am searching the source code for 'no known snapshots' and can't find it ?

[afel]

Here is a better formated steps to reproduce the bug

Create test table and fill it with data

-- create table
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 function to read data
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;

Create test class

Create file named Main.java: (change the URL and credentials)

import java.sql.*;

public class Main {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql:https://localhost/postgres?defaultRowFetchSize=50";
        Connection conn = DriverManager.getConnection(url, "user", "pwd");
        conn.setAutoCommit(false);
        int cnt = 0;
        try (CallableStatement stmt = conn.prepareCall("{? = call test_blob()}")) {
            stmt.registerOutParameter(1, Types.REF_CURSOR);
            stmt.setFetchSize(0); // has no effect
            stmt.execute();
            ResultSet rs = (ResultSet) stmt.getObject(1);
            rs.setFetchSize(0); // has no effect
            while (rs.next()) {
                cnt++;
            }
        } finally {
            System.out.println("records read: " + cnt);
        }
        conn.close();
    }
}

Compile and run

1. Add the driver jar right near the Main.java file (eg postgresql-42.3.1.jar)
2. Compile class with command javac Main.java
3. Run the app with command java -cp .;postgresql-42.3.1.jar Main

Expected output is
records read: 101

But instead I got
records read: 50 Exception in thread "main" org.postgresql.util.PSQLException: ERROR: cannot fetch toast data without an active snapshot at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)

Environment

• openjdk 11.0.11 2021-04-20
• PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit

[afel]

What version of PostgreSQL is this. I am searching the source code for 'no known snapshots' and can't find it ?

I got the "ERROR: cannot fetch toast data without an active snapshot" on PostgreSQL 14.0
And "ERROR: no known snapshots" on PostgreSQL 11.5

[davecramer]

@afel thanks for the test case.
Pretty sure this is an incompatibility with the driver and functions, but will confirm.

[davecramer]

Here's the comment from the backend code that is relevant. Still working on it, but notarizing as I go

/*
	 * GetOldestSnapshot returns NULL if the session has no active snapshots.
	 * We can get that if, for example, a procedure fetches a toasted value
	 * into a local variable, commits, and then tries to detoast the value.
	 * Such coding is unsafe, because once we commit there is nothing to
	 * prevent the toast data from being deleted.  Detoasting *must* happen in
	 * the same transaction that originally fetched the toast pointer.  Hence,
	 * rather than trying to band-aid over the problem, throw an error.  (This
	 * is not very much protection, because in many scenarios the procedure
	 * would have already created a new transaction snapshot, preventing us
	 * from detecting the problem.  But it's better than nothing, and for sure
	 * we shouldn't expend code on masking the problem more.)
	 */

[davecramer]

OK, found the issue, apparently we close the refcursor right away and only read 50 rows because of fetch size.

[davecramer]

@afel #2371
Your test succeeds with this.
Thanks for the test case. Please test if you can. I will add the testcase to it shortly

[afel]

@davecramer
Thank you for the quick fix.
Since the real bug occurs only on production ENV, and is really hardly to reproduce (happens once or twice a month), I will be able to test it only using new pgjdbc release.
Will come here after a month or two to provide feedback