pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
12+ messages / 5 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-10-29 18:21 "mirraj2 (@mirraj2)" <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: mirraj2 (@mirraj2) @ 2022-10-29 18:21 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-10-30 11:46 "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2022-10-30 11:46 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@mirraj2
Thanks for the report, sounds like we have a bug there somewhere. I should be able to look at this shortly
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-10-31 18:03 "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2022-10-31 18:03 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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 ?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-11-02 21:41 "mirraj2 (@mirraj2)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: mirraj2 (@mirraj2) @ 2022-11-02 21:41 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-11-02 22:05 "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2022-11-02 22:05 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@mirraj2
You are correct. It works if you use properties, does not work if you use the connection string
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-11-03 10:56 "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2022-11-03 10:56 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-11-03 11:41 "vlsi (@vlsi)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: vlsi (@vlsi) @ 2022-11-03 11:41 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-11-03 11:53 "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2022-11-03 11:53 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
The example he has is using autocommit(false) AFAICT ?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-11-03 12:06 "vlsi (@vlsi)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: vlsi (@vlsi) @ 2022-11-03 12:06 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I must have been confused with conn.setAutoCommit(false); in the expected section
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2022-11-03 14:37 "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2022-11-03 14:37 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2023-02-21 22:57 "ash211 (@ash211)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: ash211 (@ash211) @ 2023-02-21 22:57 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
If you remove the maxResultBuffer setting then I think adaptiveFetch is disabled.
Per https://github.com/pgjdbc/pgjdbc/blob/2b90ad04696324d107b65b085df4b1db8f6c162d/README.md?plain=1#L14..., `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).
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
@ 2025-12-09 21:36 "pkernevez (@pkernevez)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: pkernevez (@pkernevez) @ 2025-12-09 21:36 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-12-09 21:36 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-10-29 18:21 [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming "mirraj2 (@mirraj2)" <[email protected]>
2022-10-30 11:46 ` "davecramer (@davecramer)" <[email protected]>
2022-10-31 18:03 ` "davecramer (@davecramer)" <[email protected]>
2022-11-02 21:41 ` "mirraj2 (@mirraj2)" <[email protected]>
2022-11-02 22:05 ` "davecramer (@davecramer)" <[email protected]>
2022-11-03 10:56 ` "davecramer (@davecramer)" <[email protected]>
2022-11-03 11:41 ` "vlsi (@vlsi)" <[email protected]>
2022-11-03 11:53 ` "davecramer (@davecramer)" <[email protected]>
2022-11-03 12:06 ` "vlsi (@vlsi)" <[email protected]>
2022-11-03 14:37 ` "davecramer (@davecramer)" <[email protected]>
2023-02-21 22:57 ` "ash211 (@ash211)" <[email protected]>
2025-12-09 21:36 ` "pkernevez (@pkernevez)" <[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