Re: Queries with large ResultSets

There's some confusion as to whether a cursor is materialized even
inside a transaction. It could be that complicated queries will be
stored on the disk too.

Tom ?

Dave
On Thu, 2004-05-20 at 18:54, Oliver Jowett wrote:
> Andrea Aime wrote:
>
> > Ugh... those limitation are really frightening, this means we cannot fetch
> > big quantities of data outside of a transaction... this is a problem with
> > application servers like GeoServer that keep a connection pool and
> > need to fetch big quantities of data also outside a transaction... any hope
> > to see this fixed soon? Is it a driver problem or a server limitation?
>
> Cursor are implicitly closed at the end of a transaction unless they are
> declared WITH HOLD. Declaring a cursor WITH HOLD has an associated cost
> on the backend (namely it will copy the cursor's contents at the end of
> the transaction). If autocommit is on, you have an implicit transaction
> around every query, so it doesn't make sense to use a non-holdable
> cursor with autocommit on -- you'd never be able to fetch any results.
>
> This could be controllable via the JDBC3 resultset holdability methods,
> but currently it isn't and all resultsets effectively default to
> ResultSet.CLOSE_CURSORS_AT_COMMIT.
>
> I don't think you want a holdable cursor for this case anyway since the
> backend would end up doing a lot of unnecessary copying results around.
> If you're accessing big quantities of data, the overhead of an explicit
> commit() after you're done with the resultset is going to be
> insignificant compared to the cost of actually transferring and handling
> that data. Use something like this:
>
> connection.setAutoCommit(false);
> PreparedStatement stmt = connection.prepareStatement("SELECT ....");
> ResultSet rs = stmt.executeQuery();
> while (rs.next()) {
> // process data
> }
> rs.close();
> connection.commit();
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
>
> !DSPAM:40ad3936130991925076984!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561