Re: Autocommit, isolation level, and vacuum behavior

Tomasz Ostrowski wrote:
> On 2008-09-11 18:03, Jack Orenstein wrote:
>
>>> When you do:
>>> result = query("select something from sometable")
>>> then all rows of a result will be cached by a client program.
>> I am very sure this is not happening. Maybe some rows are being
>> cached (specifying fetch size), but certainly not all of them. It
>> used to, with older drivers, (7.4?) but I've been using 8.1 drivers
>> (at least) for a long time. Maybe some result set options you're
>> using cause such memory usage?
>
> Wanna bet?
>
> http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor
> | There a number of restrictions which will make the driver silently
> | fall back to fetching the whole ResultSet at once. (...) 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.
>
> So, when you turn on autocommit then it is caching it all. Fetch size is
> ignored.

Well that explains what I've been seeing (autocommit on scan producing behavior
that looks like SERIALIZABLE). Not the behavior I would prefer, but I understand
it now.

Jack