Subject: [pgjdbc/pgjdbc] issue #2861: setFetchSize is ignored?

From: "SuperPat45 (@SuperPat45)" <[email-masked]>
Date: Tue, 21 Mar 2023 15:48:48 +0000

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

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 21 Mar 2023 16:18:01 +0000

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.

---

From: "halfninja (@halfninja)" <[email-masked]>
Date: Fri, 12 Jul 2024 06:05:57 +0000

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?

---

From: "SuperPat45 (@SuperPat45)" <[email-masked]>
Date: Fri, 12 Jul 2024 10:04:07 +0000

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.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 12 Jul 2024 14:55:50 +0000

A better solution would be to implement WITH HOLD CURSORS. Anyone ?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 12 Jul 2024 14:56:44 +0000

> 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`

---

From: "ShenFeng312 (@ShenFeng312)" <[email-masked]>
Date: Tue, 09 Dec 2025 10:40:41 +0000

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

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 09 Dec 2025 17:59:50 +0000

@ShenFeng312 , PRs welcome. As far as I understand it is not as simple as "just do not buffer everything".

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 09 Dec 2025 18:03:35 +0000

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/AADDH5RYLFBL7WKTVFPUG5T4A4E2ZAVCNFSM6AAAAACOPKRIPGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTMMZTGU2DMOJVGY>
> .
> You are receiving this because you were mentioned.Message ID:
> ***@***.***>
>

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 09 Dec 2025 18:09:47 +0000

The protocol still has options to fetch data in the streaming way, so we could overcome OOM even without protocol changes.