Обсуждение: Resultset holdability
Hello, I have a resultset that has many rows with bytea data. It looks like me that when I iterate through the rows, the previous rows are only garbage collectable when the resultset is closed, even if I setup the statement like this: statement.setFetchDirection(ResultSet.FETCH_FORWARD); statement.setFetchSize(1); Since it's bytea data, this fills up the memory very quickly. Any thoughts on how to reduce this memory overhead and have the resultset discard any previously fetched data? Thanks for the help, Geert -- Geert Bevin Uwyn bvba "Use what you need" Avenue de Scailmont 34 http://www.uwyn.com 7170 Manage, Belgium gbevin[remove] at uwyn dot com Tel +32 64 84 80 03 PGP Fingerprint : 4E21 6399 CD9E A384 6619 719A C8F4 D40D 309F D6A9 Public PGP key : available at servers pgp.mit.edu, wwwkeys.pgp.net
Geert Bevin wrote: > I have a resultset that has many rows with bytea data. > > It looks like me that when I iterate through the rows, the previous > rows are only garbage collectable when the resultset is closed, even if > I setup the statement like this: > statement.setFetchDirection(ResultSet.FETCH_FORWARD); > statement.setFetchSize(1); > > Since it's bytea data, this fills up the memory very quickly. Any > thoughts on how to reduce this memory overhead and have the resultset > discard any previously fetched data? What driver version are you using? What server version are you using? Have you turned autocommit off? -O
On 21-okt-05, at 23:02, Oliver Jowett wrote: > Geert Bevin wrote: > > >> I have a resultset that has many rows with bytea data. >> It looks like me that when I iterate through the rows, the >> previous rows are only garbage collectable when the resultset is >> closed, even if I setup the statement like this: >> statement.setFetchDirection(ResultSet.FETCH_FORWARD); >> statement.setFetchSize(1); >> Since it's bytea data, this fills up the memory very quickly. Any >> thoughts on how to reduce this memory overhead and have the >> resultset discard any previously fetched data? Btw, it definitely is the resultset, since instead of using it to iterate through the results I tried doing it by running a prepared statement for each row and obtaining the results like this. The frontend logic remained the same and I have no memory problems anymore, except that there are an awful lot of queries now :-( > What driver version are you using? I tried with both 8.0-313 JDBC 3 and 8.1dev-402 JDBC 3 > What server version are you using? 8.0.3 > Have you turned autocommit off? No -- Geert Bevin Uwyn bvba "Use what you need" Avenue de Scailmont 34 http://www.uwyn.com 7170 Manage, Belgium gbevin[remove] at uwyn dot com Tel +32 64 84 80 03 PGP Fingerprint : 4E21 6399 CD9E A384 6619 719A C8F4 D40D 309F D6A9 Public PGP key : available at servers pgp.mit.edu, wwwkeys.pgp.net
Oliver Jowett wrote: > Geert Bevin wrote: > >> I have a resultset that has many rows with bytea data. >> >> It looks like me that when I iterate through the rows, the previous >> rows are only garbage collectable when the resultset is closed, even >> if I setup the statement like this: >> statement.setFetchDirection(ResultSet.FETCH_FORWARD); >> statement.setFetchSize(1); >> >> Since it's bytea data, this fills up the memory very quickly. Any >> thoughts on how to reduce this memory overhead and have the resultset >> discard any previously fetched data? > > > What driver version are you using? > What server version are you using? > Have you turned autocommit off? One more: Is the statement/resultset TYPE_FORWARD_ONLY? -O
> One more: > > Is the statement/resultset TYPE_FORWARD_ONLY? Yes. -- Geert Bevin Uwyn bvba "Use what you need" Avenue de Scailmont 34 http://www.uwyn.com 7170 Manage gbevin[remove] at uwyn dot com Tel +32 64 84 80 03 PGP Fingerprint : 4E21 6399 CD9E A384 6619 719A C8F4 D40D 309F D6A9 Public PGP key : available at servers pgp.mit.edu, wwwkeys.pgp.net
Geert Bevin wrote: >> Have you turned autocommit off? > > No The driver won't use portal-based resultsets unless you turn off autocommit. This is because it can't create the equivalent of a WITH HOLD cursor via protocol-level portals, so any portal created will be closed at the end of the creating transaction. With autocommit on, the transaction ends immediately after statement execution, so using a portal is pointless. Otherwise it sounds like you meet all the requirements (V3 protocol, TYPE_FORWARD_ONLY, and fetchsize > 0). -O
Thanks a lot Oliver, turning off autocommit seems to work :-) On 22-okt-05, at 01:01, Oliver Jowett wrote: > Geert Bevin wrote: > > >>> Have you turned autocommit off? >>> >> No >> > > The driver won't use portal-based resultsets unless you turn off > autocommit. This is because it can't create the equivalent of a > WITH HOLD cursor via protocol-level portals, so any portal created > will be closed at the end of the creating transaction. With > autocommit on, the transaction ends immediately after statement > execution, so using a portal is pointless. > > Otherwise it sounds like you meet all the requirements (V3 > protocol, TYPE_FORWARD_ONLY, and fetchsize > 0). > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > -- Geert Bevin Uwyn bvba "Use what you need" Avenue de Scailmont 34 http://www.uwyn.com 7170 Manage, Belgium gbevin[remove] at uwyn dot com Tel +32 64 84 80 03 PGP Fingerprint : 4E21 6399 CD9E A384 6619 719A C8F4 D40D 309F D6A9 Public PGP key : available at servers pgp.mit.edu, wwwkeys.pgp.net