Message-ID: <gh-pgjdbc-pgjdbc-2656-c1301978405@github.com>
From: "vlsi (@vlsi)" <noreply+vlsi@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Thu, 03 Nov 2022 11:41:55 +0000
Subject: Re: [pgjdbc/pgjdbc] issue #2656: Adaptive Fetch doesn't enable ResultSet streaming
In-Reply-To: <gh-pgjdbc-pgjdbc-2656@github.com>
References: <gh-pgjdbc-pgjdbc-2656@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: vlsi
X-GitHub-Comment-Id: 1301978405
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 2656
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/2656#issuecomment-1301978405
Content-Type: text/plain; charset=utf-8

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