pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
10+ messages / 5 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2023-03-21 15:48 "SuperPat45 (@SuperPat45)" <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: SuperPat45 (@SuperPat45) @ 2023-03-21 15:48 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Please read https://stackoverflow.com/help/minimal-reproducible-example
**Describe the issue**
Due to a bug, I have a query that returned 1 million rows instead of 10000....
I specified that I wanted the database to return only 100 rows at a time.
I noticed that the memory was full before the executeQuery() function returned the ResultSet...
Looking with JVisualVM I saw hundreds of thousands instances of the org.postgresql.core.Tuple class
So, It seem that all rows are fetched ignoring the fetchSize limit.
**Driver Version?**
42.5.0
**Java Version?**
17.0.4
**OS Version?**
Windows 10
**PostgreSQL Version?**
14.5
**To Reproduce**
```
Statement statement = connection.createStatement();
statement.setFetchSize(100);
ResultSet resultSet = statement.executeQuery("select * from one_million_row_table");
System.out.println("OK");
```
**Expected behaviour**
The result set should have been returned by the executeQuery() function without exploding the memory as I specified to only fetch 100 rows at a time.
I use the result set to write data in a text file so, in this case, the memory should never have been filled completely.
**Logs**
java.lang.OutOfMemoryError: Java heap space
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2023-03-21 16:18 "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2023-03-21 16:18 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
There are a number of conditions documented in https://jdbc.postgresql.org/documentation/query/ that are pre-requisites to fetch size being honoured.
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2024-07-12 06:05 "halfninja (@halfninja)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: halfninja (@halfninja) @ 2024-07-12 06:05 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I wondered if there is any option available to swap the silent fall-back with an exception? I'm well aware of the requirements for getting a cursor but due to abstractions like Hibernate it sometimes isn't obvious where they've been met, and in many of those cases it's preferable to fail fast than to load millions of rows into memory!
If there isn't currently an option available, does it seem like something that would be accepted as a pull request?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2024-07-12 10:04 "SuperPat45 (@SuperPat45)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: SuperPat45 (@SuperPat45) @ 2024-07-12 10:04 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
A solution would be to take account of the fetchSize despite the autocommit mode, but as soon as the first commit occurs, load all the remaining data into all the opened ResultSets to avoid errors.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2024-07-12 14:55 "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2024-07-12 14:55 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
A better solution would be to implement WITH HOLD CURSORS. Anyone ?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2024-07-12 14:56 "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2024-07-12 14:56 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> I wondered if there is any option available to swap the silent fall-back with an exception? I'm well aware of the requirements for getting a cursor but due to abstractions like Hibernate it sometimes isn't obvious where they've been met, and in many of those cases it's preferable to fail fast than to load millions of rows into memory!
>
> If there isn't currently an option available, does it seem like something that would be accepted as a pull request?
I'd be interested in a PR which implemented `WITH HOLD CURSORS`
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2025-12-09 10:40 "ShenFeng312 (@ShenFeng312)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: ShenFeng312 (@ShenFeng312) @ 2025-12-09 10:40 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I’m curious why we can’t, like MySQL, fetch the next batch of data from the TCP receive buffer only when calling `ResultSet.next()`.@davecramer @vlsi
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2025-12-09 17:59 "vlsi (@vlsi)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: vlsi (@vlsi) @ 2025-12-09 17:59 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@ShenFeng312 , PRs welcome. As far as I understand it is not as simple as "just do not buffer everything".
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2025-12-09 18:03 "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2025-12-09 18:03 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
The problem is the way the protocol works. Currently if we don’t use a
cursor all the results are returned
Dave Cramer
On Tue, Dec 9, 2025 at 1:00 PM Vladimir Sitnikov ***@***.***>
wrote:
> *vlsi* left a comment (pgjdbc/pgjdbc#2861)
> <https://github.com/pgjdbc/pgjdbc/issues/2861#issuecomment-3633546956;
>
> @ShenFeng312 <https://github.com/ShenFeng312; , PRs welcome. As far as I
> understand it is not as simple as "just do not buffer everything".
>
> —
> Reply to this email directly, view it on GitHub
> <https://github.com/pgjdbc/pgjdbc/issues/2861#issuecomment-3633546956;,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AADDH5RYLFBL7WKTVFPUG5T4A4E2ZAVCNFSM6AAAAACOPKRIPG...;
> .
> You are receiving this because you were mentioned.Message ID:
> ***@***.***>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?
@ 2025-12-09 18:09 "vlsi (@vlsi)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: vlsi (@vlsi) @ 2025-12-09 18:09 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
The protocol still has options to fetch data in the streaming way, so we could overcome OOM even without protocol changes.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-12-09 18:09 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-21 15:48 [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored? "SuperPat45 (@SuperPat45)" <[email protected]>
2023-03-21 16:18 ` "davecramer (@davecramer)" <[email protected]>
2024-07-12 06:05 ` "halfninja (@halfninja)" <[email protected]>
2024-07-12 10:04 ` "SuperPat45 (@SuperPat45)" <[email protected]>
2024-07-12 14:55 ` "davecramer (@davecramer)" <[email protected]>
2024-07-12 14:56 ` "davecramer (@davecramer)" <[email protected]>
2025-12-09 10:40 ` "ShenFeng312 (@ShenFeng312)" <[email protected]>
2025-12-09 17:59 ` "vlsi (@vlsi)" <[email protected]>
2025-12-09 18:03 ` "davecramer (@davecramer)" <[email protected]>
2025-12-09 18:09 ` "vlsi (@vlsi)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox