Re: ResultSet storing all rows with defaulftFetchSize to 5000 - Mailing list pgsql-jdbc

Well testing with last version v42.2.1 I see same behaviour,  org.postgresql.jdbc.PgStatement.java and org.postgresql.core.v3.QueryExecutorImpl.java has same condition to use Portal as version 9.4.1208:

private void executeInternal(CachedQuery cachedQuery, ParameterList queryParameters, int flags)

      throws SQLException {

    closeForNextExecution();

    // Enable cursor-based resultset if possible.

    if (fetchSize > 0 && !wantsScrollableResultSet() && !connection.getAutoCommit()

        && !wantsHoldableResultSet()) {

      flags |= QueryExecutor.QUERY_FORWARD_CURSOR;

    }

private void sendOneQuery(SimpleQuery query, SimpleParameterList params, int maxRows,

      int fetchSize, int flags) throws IOException {

...

    boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta

        && fetchSize > 0 && !describeOnly;

I am helping to migrate a Oracle application to run on Postgresql, and the default holdability setting at oracle is HOLD_CURSOR_OVER_COMMIT so I have tried to set this  HOLD_CURSOR_OVER_COMMIT as default holdability setting. Could you explain me why I couldn't use fetchSize in a transaction( autocommit = false ) and keeping those ResultSet available after a commit ? is there a limitation of the jdbc driver or it comes from server side ? 

What the application does is something similar to:

postgres=# begin;

BEGIN

edb=# declare p cursor with hold for select * from foo;

DECLARE CURSOR

postgres=# fetch next p;

 id 

----

  1

postgres=# fetch next p;

 id 

----

  2

postgres=# fetch next p;

 id 

----

  3

postgres=# fetch next p;

 id 

----

  4

postgres=# commit;

COMMIT

-- after validating a chunk of data, continue with the next chunk 

postgres=# begin;

BEGIN

postgres=# fetch next p;

 id 

----

  5

postgres=# fetch next p;

 id 

----

  6

postgres=# commit;

Regards

2018-02-09 23:09 GMT+01:00 Dave Cramer <pg@fastcrypt.com>:

Jaime,

9.4.1208 is considerably out of date. You may want to upgrade.

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 8 February 2018 at 19:13, Brad DeJong <bpd0018@gmail.com> wrote:

On Wed, Feb 7, 2018 at 1:00 PM, Jaime Soler <jaime.soler@gmail.com> wrote:

... ResultSet holdability is HOLD_CURSOR_OVER_COMMIT. ...

And the postgresql comunity jdbc driver 9.4 documentation said:

"... There are a number of restrictions which will make the driver silently fall back to fetch the whole ResultSet ..."

And the statement must be created with a ResultSet holdability of CLOSE_CURSORS_AT_COMMIT - which is not documented. https://github.com/pgjdbc/pgjdbc/pull/1105 submitted to fix that.

The relevant code fragment from org.postgresql.jdbc.PgStatement.java is ...

    // Enable cursor-based resultset if possible.

    if (fetchSize > 0 && !wantsScrollableResultSet() && !connection.getAutoCommit()

        && !wantsHoldableResultSet()) {

      flags |= QueryExecutor.QUERY_FORWARD_CURSOR;

    }

And the fragment from org.postgresql.v3.QueryExecutorImpl.java ...

    boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta

        && fetchSize > 0 && !describeOnly;