Re: ResultSet storing all rows with defaulftFetchSize to 5000 - Mailing list pgsql-jdbc
| From | Jaime Soler |
|---|---|
| Subject | Re: ResultSet storing all rows with defaulftFetchSize to 5000 |
| Date | |
| Msg-id | CAKVUGgQj-cT4UVQQTKpN4ZAj_ikqWoqaKCpOvSQDXA8zdunaRA@mail.gmail.com Whole thread Raw |
| In response to | Re: ResultSet storing all rows with defaulftFetchSize to 5000 (Dave Cramer <pg@fastcrypt.com>) |
| Responses |
Re: ResultSet storing all rows with defaulftFetchSize to 5000
Re: ResultSet storing all rows with defaulftFetchSize to 5000 |
| List | pgsql-jdbc |
Well testing with last version v42.2.1 I see same behaviour, org.postgresql.jdbc.
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.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;
pgsql-jdbc by date: