| From: | Jaime Soler <jaime(dot)soler(at)gmail(dot)com> |
|---|---|
| To: | List <pgsql-jdbc(at)postgresql(dot)org> |
| Subject: | ResultSet storing all rows with defaulftFetchSize to 5000 |
| Date: | 2018-02-07 19:00:02 |
| Message-ID: | CAKVUGgSRwC=dNXL0T6vq2eH6ChwAf5FjCQD5A=9J9oO46-7VEg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-jdbc |
An application is using the postgresql comunity jdbc driver v 9.4.1208 and
I faced a problem because after analysing a java heap dump of the execution
of big batch java procedure, I discover that the jdbc driver is keeping at
the keep every row fetched. The jvm heap contained 1,2M rows and we have
setup a defaultFetchSize of 5000 rows also I share you adiciontal detail,
autocommit is false, ResultSet is TYPE_FORWARD_ONLY , ResultSet
holdability is HOLD_CURSOR_OVER_COMMIT.
And the postgresql comunity jdbc driver 9.4 documentation said:
"
By default the driver collects all the results for the query at once. This
can be inconvenient for large data sets so the JDBC driver provides a means
of basing a ResultSet on a database cursor and only fetching a small number
of rows.
A small number of rows are cached on the client side of the connection and
when exhausted the next block of rows is retrieved by repositioning the
cursor.
Note
Cursor based ResultSets cannot be used in all situations. There a number of
restrictions which will make the driver silently fall back to fetching the
whole ResultSet at once.
*The connection to the server must be using the V3 protocol. This is the
default for (and is only supported by) server versions 7.4 and later.
*The Connection must not be in autocommit mode. The backend closes cursors
at the end of transactions, so in autocommit mode the backend will have
closed the cursor before anything can be fetched from it.
*The Statement must be created with a ResultSet type of
ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need to
be rewritten to take advantage of this, but it also means that you cannot
scroll backwards or otherwise jump around in the ResultSet.
*The query given must be a single statement, not multiple statements strung
together with semicolons.
"
why the driver is storing so many rows because I think my connections
satisfy all the conditions to ONLY store a limit number of rows.
Regards
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Brad DeJong | 2018-02-09 00:13:17 | Re: ResultSet storing all rows with defaulftFetchSize to 5000 |
| Previous Message | bpd0018 | 2018-02-02 20:11:40 | [pgjdbc/pgjdbc] 0cfffa: docs: fix spelling and chapter, update sample code... |