Thread: implementation of Statement.executeQuery()

implementation of Statement.executeQuery()

From
Jonathan Gold
Date:
i know there has been large discussion in the last year or two about
the fact that the code

  ResultSet rs = someStatement.executeQuery( queryString )

causes an OutOfMemoryException for large result sets, as the backend
returns the entire result set at once, and so it must be stored in
memory while the result set exists. the current accepted solution
seems to be the use of a result set in combination with two prepared
statements -- one to create a cursor, the other to fetch from it.
there was some comment in these threads that it would be nice if that
could be fixed, and to have this functionality added to the jdbc
implementation for postgres.

what is the state of this? do people still want to do it? if so, is
anyone working on it yet?

jon

Re: implementation of Statement.executeQuery()

From
Dave Cramer
Date:
Jon,

It has been implemented, get the latest driver.

Dave
On Mon, 2004-01-19 at 19:40, Jonathan Gold wrote:
> i know there has been large discussion in the last year or two about
> the fact that the code
>
>   ResultSet rs = someStatement.executeQuery( queryString )
>
> causes an OutOfMemoryException for large result sets, as the backend
> returns the entire result set at once, and so it must be stored in
> memory while the result set exists. the current accepted solution
> seems to be the use of a result set in combination with two prepared
> statements -- one to create a cursor, the other to fetch from it.
> there was some comment in these threads that it would be nice if that
> could be fixed, and to have this functionality added to the jdbc
> implementation for postgres.
>
> what is the state of this? do people still want to do it? if so, is
> anyone working on it yet?
>
> jon
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
--
Dave Cramer
519 939 0336
ICQ # 1467551


Re: implementation of Statement.executeQuery()

From
Oliver Jowett
Date:
Jonathan Gold wrote:
> i know there has been large discussion in the last year or two about
> the fact that the code
>
>   ResultSet rs = someStatement.executeQuery( queryString )
>
> causes an OutOfMemoryException for large result sets, as the backend
> returns the entire result set at once, and so it must be stored in
> memory while the result set exists. the current accepted solution
> seems to be the use of a result set in combination with two prepared
> statements -- one to create a cursor, the other to fetch from it.
> there was some comment in these threads that it would be nice if that
> could be fixed, and to have this functionality added to the jdbc
> implementation for postgres.
>
> what is the state of this? do people still want to do it? if so, is
> anyone working on it yet?

The current driver will automatically use cursors behind the scenes if
all of these conditions are true:

  1) the statement's fetchsize (via setFetchSize()) is greater than 0
  2) the query is a single SELECT
  3) autocommit is off, and
  4) the statement produces non-scrollable resultsets (actually I think
currently it'll use cursors for scrollable resultsets, but the resultset
doesn't actually work correctly).

As part of the V3 protocol work I'm starting on now, it might be
possible to relax some of these restrictions. specifically:

  1) could be made the default more easily
  2) can be relaxed to any query that produces results (probably
multistatement queries, too)

3) can be "fixed" by implementing support for JDBC3's holdable
resultsets (if the server supports holdable cursors).

4) is a bug; I have a patch pending (maybe applied now?) that works
around it just by disabling cursors for scrollable resultsets. We need a
proper fix here.

-O

Re: implementation of Statement.executeQuery()

From
Kris Jurka
Date:

On Tue, 20 Jan 2004, Oliver Jowett wrote:
>
> The current driver will automatically use cursors behind the scenes if
> all of these conditions are true:
>
>   4) the statement produces non-scrollable resultsets (actually I think
> currently it'll use cursors for scrollable resultsets, but the resultset
> doesn't actually work correctly).
>
> 4) is a bug; I have a patch pending (maybe applied now?) that works
> around it just by disabling cursors for scrollable resultsets. We need a
> proper fix here.
>

I have applied this patch to the cvs version of the driver on gborg.

Kris Jurka