Re: Out of memory error on huge resultset - Mailing list pgsql-jdbc
From | snpe |
---|---|
Subject | Re: Out of memory error on huge resultset |
Date | |
Msg-id | 200210112318.03841.snpe@snpe.co.yu Whole thread Raw |
In response to | Re: Out of memory error on huge resultset (Dave Cramer <Dave@micro-automation.net>) |
List | pgsql-jdbc |
I test Oracle JDeveloper and jdbc driver for postgresql work fine now Meanwhile, for production systems I have to have setFetchSize for large tables I think that it is same with any Java IDE. Best solution is that we have only n rows from backend, but I don't know is it possible regards Haris Peco On Friday 11 October 2002 10:38 pm, Dave Cramer wrote: > Looking at their code, default fetch size is 1000? > > Anyways, I think there is sufficient interest in this that we should > have something running soon here > > Dave > > On Fri, 2002-10-11 at 17:02, snpe wrote: > > I am tried with jxdbcon - it don't work with large table, too. > > 'out of memory' is when executeQuery() > > > > regards > > Haris Peco > > > > On Friday 11 October 2002 10:33 pm, snpe wrote: > > > There is jxdbcon Postgresql jdbc driver with setFetchSize method. > > > Last version don't wokr with pgsql 7.3 and I don't test more. > > > I will try next day, when I download pgsql 7.2 > > > > > > regards > > > Haris Peco > > > > > > On Friday 11 October 2002 07:59 pm, Dave Cramer wrote: > > > > Agreed, but there are selects where count(*) won't work. Even so, > > > > what we are talking about here is hiding the implementation of > > > > cursors behind the result set. What I would envision is some sort of > > > > cacheing where when the user set's the fetchsize to 10 for instance > > > > we do the select, and when they ask for next() we check to see if we > > > > have these rows in the cache, and go get them if necessary 10 at a > > > > time, possibly keeping one set of ten behind where we are and one set > > > > of 10 ahead of where we are. So recalling that resultSets have > > > > absolute positioning, as well as first(), and last() positioning we > > > > need the ability to move with the minimum number of trips to the > > > > backend. > > > > > > > > As it turns out the move command in postgres does support moving to > > > > the end (move 0 ); at the moment this is considered a bug, and is on > > > > the todo list to be removed. I expect we can get some sort of > > > > implementation which allows us to move to the end ( move end ) > > > > > > > > Dave > > > > > > > > On Fri, 2002-10-11 at 13:12, Dror Matalon wrote: > > > > > Hi, > > > > > > > > > > I'm jumping in late into this discussion but ... > > > > > > > > > > In my mind a lot of these features break the model. From an > > > > > application prespective, if I want to do last, I do a count(*) and > > > > > then I do a fetch with limit; Not quite the same, but all these > > > > > methods of fetching the whole data locally and manipulating it to a > > > > > large exten defeat the purpose. Let the backend do the work, > > > > > instead of trying to replicate the functionality in JDBC. > > > > > > > > > > That said I do understand that some of these are required by the > > > > > JDBC 2.0 spec. > > > > > > > > > > Dror > > > > > > > > > > On Fri, Oct 11, 2002 at 01:05:37PM -0400, Aaron Mulder wrote: > > > > > > It wouldn't be bad to start with a naive implementation of > > > > > > last()... If the only problem we have is that last() doesn't > > > > > > perform well, we're probably making good progress. :) > > > > > > On the other hand, I would think the updateable result sets > > > > > > would be the most challenging; does the server provide any > > > > > > analogous features with its cursors? > > > > > > > > > > > > Aaron > > > > > > > > > > > > On 11 Oct 2002, Dave Cramer wrote: > > > > > > > This really is an artifact of the way that postgres gives us > > > > > > > the data. > > > > > > > > > > > > > > When you query the backend you get *all* of the results in the > > > > > > > query, and there is no indication of how many results you are > > > > > > > going to get. In simple selects it would be possible to get > > > > > > > some idea by using count(field), but this wouldn't work nearly > > > > > > > enough times to make it useful. So that leaves us with using > > > > > > > cursors, which still won't tell you how many rows you are > > > > > > > getting back, but at least you won't have the memory problems. > > > > > > > > > > > > > > This approach is far from trivial which is why it hasn't been > > > > > > > implemented as of yet, keep in mind that result sets support > > > > > > > things like move(n), first(), last(), the last of which will be > > > > > > > the trickiest. Not to mention updateable result sets. > > > > > > > > > > > > > > As it turns out there is a mechanism to get to the end move 0 > > > > > > > in 'cursor', which currently is being considered a bug. > > > > > > > > > > > > > > Dave > > > > > > > > > > > > > > On Fri, 2002-10-11 at 11:44, Doug Fields wrote: > > > > > > > > At 08:27 AM 10/11/2002, snpe wrote: > > > > > > > > >Barry, > > > > > > > > > Is it true ? > > > > > > > > >I create table with one column varchar(500) and enter 1 > > > > > > > > > milion rows with length 10-20 character.JDBC query 'select > > > > > > > > > * from a' get error 'out of memory', but psql not. > > > > > > > > >I insert 8 milion rows and psql work fine yet (slow, but > > > > > > > > > work) > > > > > > > > > > > > > > > > The way the code works in JDBC is, in my opinion, a little > > > > > > > > poor but possibly mandated by JDBC design specs. > > > > > > > > > > > > > > > > It reads the entire result set from the database backend and > > > > > > > > caches it in a horrible Vector (which should really be a List > > > > > > > > and which should at least make an attempt to get the # of > > > > > > > > rows ahead of time to avoid all the resizing problems). > > > > > > > > > > > > > > > > Then, it doles it out from memory as you go through the > > > > > > > > ResultSet with the next() method. > > > > > > > > > > > > > > > > I would have hoped (but was wrong) that it streamed - WITHOUT > > > > > > > > LOADING THE WHOLE THING - through the result set as each row > > > > > > > > is returned from the backend, thus ensuring that you never > > > > > > > > use much more memory than one line. EVEN IF you have to keep > > > > > > > > the connection locked. > > > > > > > > > > > > > > > > The latter is what I expected it to do. The former is what it > > > > > > > > does. So, it necessitates you creating EVERY SELECT query > > > > > > > > which you think has more than a few rows (or which you think > > > > > > > > COULD have more than a few rows, "few" being defined by our > > > > > > > > VM memory limits) into a cursor based query. Really klugy. I > > > > > > > > intend to write a class to do that for every SELECT query for > > > > > > > > me automatically. > > > > > > > > > > > > > > > > Cheers, > > > > > > > > > > > > > > > > Doug > > > > > > > > > > > > > > > > >In C library is 'execute query' without fetch - in jdbc > > > > > > > > > execute fetch all rows > > > > > > > > >and this is problem - I think that executequery must prepare > > > > > > > > > query and fetch (ResultSet.next or ...) must fetch only > > > > > > > > > fetchSize rows. I am not sure, but I think that is problem > > > > > > > > > with jdbc, not postgresql Hackers ? > > > > > > > > >Does psql fetch all rows and if not how many ? > > > > > > > > >Can I change fetch size in psql ? > > > > > > > > >CURSOR , FETCH and MOVE isn't solution. > > > > > > > > >If I use jdbc in third-party IDE, I can't force this > > > > > > > > > solution > > > > > > > > > > > > > > > > > >regards > > > > > > > > > > > > > > > > > >On Thursday 10 October 2002 06:40 pm, Barry Lind wrote: > > > > > > > > > > Nick, > > > > > > > > > > > > > > > > > > > > This has been discussed before on this list many times. > > > > > > > > > > But the short answer is that that is how the postgres > > > > > > > > > > server handles queries. If you issue a query the server > > > > > > > > > > will return the entire result. (try the same query in > > > > > > > > > > psql and you will have the same problem). To work around > > > > > > > > > > this you can use explicit cursors (see the DECLARE > > > > > > > > > > CURSOR, FETCH, and MOVE sql commands for postgres). > > > > > > > > > > > > > > > > > > > > thanks, > > > > > > > > > > --Barry > > > > > > > > > > > > > > > > > > > > Nick Fankhauser wrote: > > > > > > > > > > > I'm selecting a huge ResultSet from our database- about > > > > > > > > > > > one million rows, with one of the fields being > > > > > > > > > > > varchar(500). I get an out of memory error from java. > > > > > > > > > > > > > > > > > > > > > > If the whole ResultSet gets stashed in memory, this > > > > > > > > > > > isn't really surprising, but I'm wondering why this > > > > > > > > > > > happens (if it does), rather than a subset around the > > > > > > > > > > > current record being cached and other rows being > > > > > > > > > > > retrieved as needed. > > > > > > > > > > > > > > > > > > > > > > If it turns out that there are good reasons for it to > > > > > > > > > > > all be in memory, then my question is whether there is > > > > > > > > > > > a better approach that people typically use in this > > > > > > > > > > > situation. For now, I'm simply breaking up the select > > > > > > > > > > > into smaller chunks, but that approach won't be > > > > > > > > > > > satisfactory in the long run. > > > > > > > > > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > > > > > > > > > > -Nick > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------------- > > > > > > > > > > >---- -- ------------ - Nick Fankhauser nickf@ontko.com > > > > > > > > > > > Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & > > > > > > > > > > > Co. Software Consulting Services http://www.ontko.com/ > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of > > > > > > > > > > > broadcast)--------------------------- TIP 5: Have you > > > > > > > > > > > checked our extensive FAQ? > > > > > > > > > > > > > > > > > > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > > > > > > > > > > > > > > > > ---------------------------(end of > > > > > > > > > > broadcast)--------------------------- TIP 6: Have you > > > > > > > > > > searched our list archives? > > > > > > > > > > > > > > > > > > > > http://archives.postgresql.org > > > > > > > > > > > > > > > > > >---------------------------(end of > > > > > > > > > broadcast)--------------------------- TIP 2: you can get > > > > > > > > > off all lists at once with the unregister command (send > > > > > > > > > "unregister YourEmailAddressHere" to > > > > > > > > > majordomo@postgresql.org) > > > > > > > > > > > > > > > > ---------------------------(end of > > > > > > > > broadcast)--------------------------- TIP 6: Have you > > > > > > > > searched our list archives? > > > > > > > > > > > > > > > > http://archives.postgresql.org > > > > > > > > > > > > > > ---------------------------(end of > > > > > > > broadcast)--------------------------- TIP 1: subscribe and > > > > > > > unsubscribe commands go to majordomo@postgresql.org > > > > > > > > > > > > ---------------------------(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 > > > > > > > > > > -- > > > > > Dror Matalon > > > > > Zapatec Inc > > > > > 1700 MLK Way > > > > > Berkeley, CA 94709 > > > > > http://www.zapatec.com > > > > > > > > > > ---------------------------(end of > > > > > broadcast)--------------------------- TIP 1: subscribe and > > > > > unsubscribe commands go to majordomo@postgresql.org > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- TIP 2: you can get off all > > > > lists at once with the unregister command (send "unregister > > > > YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > ---------------------------(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 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html
pgsql-jdbc by date: