Thread: implementation of Statement.executeQuery()
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
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
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
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