Re: Out of memory error on huge resultset

What if query have LIMIT or OFFSET before and what when user request next row
out of LIMIT or OFFSET

regards
Haris Peco
On Friday 11 October 2002 11:49 pm, Simpson, Mike W wrote:
> This definitely isn't my field, but in our code we're perfectly happy
> setting LIMIT and OFFSET.  In combination with stored procedures,
> performance is excellent.
>
> If you really want a driver change, wouldn't it be simpler to just append
> LIMIT and OFFSET?
> Anytime setFetchSize is called, just tack on
>     query+=" LIMIT "+getFetchSize()+" OFFSET "+rowPointer;
> and rs.next()/cursor code increment the rowPointer.
>
> Just my 2 cents...
> Mike
>
> -----Original Message-----
> From: snpe [mailto:snpe@snpe.co.yu]
> Sent: Friday, October 11, 2002 3:43 PM
> To: Dave Cramer
> Cc: pgsql-jdbc; PostgreSQL-development
> Subject: Re: [JDBC] Out of memory error on huge resultset
>
>
> Can You do this :
>    We save 1000 (or fetchSize rows) first from beginning
>    If table have < 1000 rows we save all rows, but if table have more rows
> and user request 1001 we fetch 1000 (again from begining, but skip 1000
> rows
>
> or maybe continue fetching, if it possible)
>   When user request last we fetch all rows, but save only last 1000 etc
>
>   We save only fetchSize rows and seek from begining when user request
> backward (or maybe seek always when user request out our 'fetchSize'
> window)
>
>   This is slow for large tables, but this is solution until developer get
> us
>
> better solution from backend.If table have < fetchSize rows this is same
> current solution and we can fix minimal fetchSize for better performance
> with
> small tables.
>
> regards
> Haris Peco
>
> On Friday 11 October 2002 08:13 pm, Dave Cramer wrote:
> > No,
> >
> > It doesn't have to store them, only display them
> >
> > Dave
> >
> > On Fri, 2002-10-11 at 12:48, snpe wrote:
> > > Hello,
> > >   Does it mean that psql uses cursors ?
> > >
> > > regards
> > > Haris Peco
> > >
> > > On Friday 11 October 2002 05:58 pm, 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 5: Have you checked our
> > > extensive FAQ?
> > >
> > > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org