Subject: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming

From: "mirraj2 (@mirraj2)" <[email-masked]>
Date: Sat, 29 Oct 2022 18:21:26 +0000

**Describe the issue**
I enabled adaptiveFetch, however, all rows are being loaded into memory instead of allowing me to stream through the ResultSet

**Driver Version?** 
42.5.0

**Java Version?**
java version "11.0.16" 2022-07-19 LTS

**OS Version?**
Mac OS 12.5

**PostgreSQL Version?**
PostgreSQL 13.7 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit

**To Reproduce**
1. Use connection string with "?adaptiveFetch=true&defaultRowFetchSize=64&maxResultBuffer=128M"
2. execute a statement which selects all rows from a large table

**Expected behaviour**
I expect that when I call ResultSet.next(), it will return fairly quickly because it has only loaded part of the table.
Instead, it takes a LONG time to load because it is loading the entire table into RAM.

When I set the following code (even without adaptiveFetch turned on), it will correctly stream results:
    conn.setAutoCommit(false);
    statement.setFetchSize(4096);

Additionally, I enabled the FINEST level of logging, but was unable to ascertain whether or not adaptiveFetch was even enabled.  This makes it very hard for me to even know if my connectionUrl params are being ignored or not.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 30 Oct 2022 11:46:57 +0000

@mirraj2 

Thanks for the report, sounds like we have a bug there somewhere. I should be able to look at this shortly

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 31 Oct 2022 18:03:15 +0000

I tested the following code and it appears to work (note this is groovy, but add semicolons and it should compile)
```
class TestFetchSize {
    public static void main(String[] args)
    {
        Properties properties = new Properties()
        properties.setProperty(PGProperty.USER.getName(),"test")
        properties.setProperty(PGProperty.PASSWORD.getName(),"password")
        properties.setProperty(PGProperty.LOGGER_LEVEL.getName(), "TRACE")
        properties.setProperty(PGProperty.PREFER_QUERY_MODE.getName(),"extended")
        PGProperty.ADAPTIVE_FETCH.set(properties, true);

        DriverManager.setLogWriter(new PrintWriter(System.err));

        Connection con = DriverManager.getConnection('jdbc:postgresql://localhost:5432/test',properties)

        con.setAutoCommit(false)

        Statement stmt = con.createStatement(); //ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)
        stmt.setFetchSize(10)
        ResultSet rs = stmt.executeQuery('select * from fetchrows')
        while (rs.next())
        {
            rs.getInt('id')
        }
        con.commit()
        con.close()

    }

}
```

I tested this code and it does actually use cursors. 

Can you share your code ?

---

From: "mirraj2 (@mirraj2)" <[email-masked]>
Date: Wed, 02 Nov 2022 21:41:07 +0000

@davecramer - the code you pasted demonstrates what I verified _does_ work correctly.  If you setFetchSize() on the Statement it will work.  The bug is not about setFetchSize().  It is about using the "adaptiveFetch" feature.  AdaptiveFetch should automatically change the fetch size based on the size of each row.

To test what I'm seeing, comment out the "setFetchSize" row, and use something like the following connection string:
jdbc:postgresql://localhost:5432/test?adaptiveFetch=true&defaultRowFetchSize=10&maxResultBuffer=128M

I would urge you to set these parameters via the connection string instead of via Properties, because maybe the bug has to do with settings via the query string.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 02 Nov 2022 22:05:31 +0000

@mirraj2 

You are correct. It works if you use properties, does not work if you use the connection string

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 03 Nov 2022 10:56:13 +0000

I was kind of hoping that the latest would have fixed this, but alas, it has not.

Thanks for the report. I'll dig into it.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 03 Nov 2022 11:41:55 +0000

> it takes a LONG time to load because it is loading the entire table into RAM.

As far as I remember, there's no way to use partial fetches in autocommit=true mode.

We might probably introduce a warning if the user code happens to fetch a huge resultset with `autocommit=true` mode.

For instance:
* `maxResultSizeBeforeWarning=512m`. In other words, if the resultset fetches more than 512M in one go, raise a warning
* `maxResultSizeBeforeError=4g`. In other words, if a resultset fetches more than 4G in one go, throw an error and discard the data. It would protect apps from running out of memory, and the workaround would be using `autoCommit=false`, or raising the limit, or asking PostgreSQL database developers to support "fetch from portal across transaction". AFAIK even named portals do not survive after the transaction commits, so the only option is to fetch all the rows at once. There's another option to "store" the data in the socket buffer (e.g. fetch 10 rows, return control to the user, and then fetch the next rows), however, it would be hard to implement, and it would effectively make the connection unusable to all the other queries.

WDYT?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 03 Nov 2022 11:53:03 +0000

The example he has is using autocommit(false) AFAICT ?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 03 Nov 2022 12:06:13 +0000

I must have been confused with conn.setAutoCommit(false); in the expected section

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 03 Nov 2022 14:37:26 +0000

So the issue is that maxResultBuffer=128M over-rides the defaultRowFetchSize.

If you remove the maxResultBuffer it should work.

This is still a bug, but at least we know what it is now

---

From: "ash211 (@ash211)" <[email-masked]>
Date: Tue, 21 Feb 2023 22:57:54 +0000

If you remove the maxResultBuffer setting then I think adaptiveFetch is disabled.

Per https://github.com/pgjdbc/pgjdbc/blob/2b90ad04696324d107b65b085df4b1db8f6c162d/README.md?plain=1#L145, `adaptiveFetch`

> Requires declaring maxResultBuffer and defaultRowFetchSize for first iteration.  

----------------

I recently ran into this, where I had set `adaptiveFetch=true` and `defaultRowFetchSize=10` and `maxResultBuffer=10percent` and yet still a user of my tool observed this failure message: `Result set exceeded maxResultBuffer limit. Received: 103887670; Current limit: 103887667`

It seems that currently this JDBC driver requests "give me N rows" and gets back those N rows, regardless of their size.  If postgres server supported such a thing, we may prefer "give me N rows or up to B bytes" and receiving 1..N rows instead of always N.  This would enable smoother streaming of a ResultSet, without OOM or hitting `maxResultBuffer` limits while still maintaining reasonably large fetch sizes (not just 1 all the time).

---

From: "pkernevez (@pkernevez)" <[email-masked]>
Date: Tue, 09 Dec 2025 21:36:02 +0000

Any news about this issue ?
It's still resent and don't find a solution to efficiently use server side cursor to limit the client footprint.