Re: OutOfMemory - Mailing list pgsql-jdbc

Guido Fiala wrote:
> By chance i'am currently at the same point, unfortunately i don't get it
> working as expected.
>
> -after calling ResultSet.last() the getRow() still reflects the fetchsize (how
> to get the number of records for e.g. a progress-bar?)

That sounds like a bug; last() should take you to the very last record
of the resultset independent of the fetchsize. Can you submit a testcase
showing this?

(... later ...) Hold on -- doesn't last() require a scrollable
resultset, which means you're not using a cursor behind the scenes? I
think the driver does not throw an exception if you try to use
last()/absolute()/etc with FETCH_FORWARD_ONLY (it should!) but instead
just gives you the wrong results.. not great, but this is not only the
fault of the driver :)

> -calling "ResultSet.next()" at the "last" fetched record does not fetch more
> results automatically, Statement.fetchMoreResults() gives me null-pointer -
> how do i actually get the next fetch?

It should be transparent. i.e. last() should take you to the *very end*
of the resultset, fetching forward until there are no more results. You
shouldn't ever see the boundaries between fetches (other than as a
slight delay on next()).

> -according to the documentation only "FETCH_FORWARD" is supported, which is
> not always suitable

This is simply because no-one has implemented the logic to support
scrollable resultsets backed by a cursor yet. Patches are welcome!

> Does it have some meaning that it only works if
> Connection.setAutoCommit(false) is used?

Yes -- otherwise we'd need to use a cursor WITH HOLD and manage it more
carefully. Currently we rely on end-of-transaction closing the cursors,
and that scheme doesn't work with autocommit on so we don't use cursors
in that case.

This is actually a tradeoff between storing the resultset on the backend
and storing it on the java process -- WITH HOLD is not free, someone has
to store the data once the transaction is gone. I think the backend does
a better job of paging out results etc though.

> I had a quick look through the sources and found the term
> "server-prepared-statement" is used under certain conditions - what's this
> for?

That's unrelated to cursor use; this is to do with transforming
repeatedly executed queries into a PREPARE/EXECUTE form. It's not on by
default.

> I was also thinking about using the "SELECT ... LIMIT x OFFSET y" instead, but
> this might lead to unexpected side effects if multiple users are changing
> data - the user gets only a momentary snapshot then, if the order has changed
> in between some records will never be seen, others twice and so on.
>
> Any nice idea to solve this?

Wrap your queries in a transaction if you want transactional isolation.
Also you really want an ORDER BY if you're using LIMIT/OFFSET.

-O