Re: ResultSet storing all rows with defaulftFetchSize to 5000

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.
>>
>> 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;
>>
>>
>