Re: Driver memory usage on select and autocommit

Silvio Bierman wrote:
> Can anyone explain why the PostgreSQL JDBC driver caches the result of a
> select statement in memory even though I have set a fetch size. Only
> setting autocommit to off will prevent this but that seems very
> counterintuitive.
>
> Any explanations?

http://jdbc.postgresql.org/documentation/83/query.html#fetchsize-example

Incremental retrieval of results relies on using a v3 protocol portal
(similar to a cursor). These portals are implicitly closed at the end of
a transaction; that includes the end of the implicit transaction created
for each statement when autocommit is on. So you have to have a
long-lived transaction for incremental retrieval to be useful, i.e.
autocommit must be off.

-O