Re: ResultSet storing all rows with defaulftFetchSize to 5000

On 12 February 2018 at 10:11, Jaime Soler <jaime(dot)soler(at)gmail(dot)com> wrote:

> 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(at)fastcrypt(dot)com>:
>
>> Jaime,
>>
>> 9.4.1208 is considerably out of date. You may want to upgrade.
>>
>> Dave Cramer
>>
>> davec(at)postgresintl(dot)com
>> www.postgresintl.com
>>
>> On 8 February 2018 at 19:13, Brad DeJong <bpd0018(at)gmail(dot)com> wrote:
>>
>>>
>>>
>>> On Wed, Feb 7, 2018 at 1:00 PM, Jaime Soler <jaime(dot)soler(at)gmail(dot)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.
>>>
>>>
Thanks

> 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;
>>>
>>>
>>
> Without seeing your code I can only direct you to
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/test/java/org/postgresql/test/jdbc2/CursorFetchTest.java

Where we test setFetchSize. If you can provide a self contained test that
we can debug that would be useful

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com