Re: streaming result sets: progress - Mailing list pgsql-jdbc

Yes, proccess increase with result from server and diferent is that
C request less memory - in C we can execute big qyery than Java
I can't believe that we must complete query in memory, but it is true
Excuse me

Thanks

On Wednesday 20 November 2002 05:51 pm, Barry Lind wrote:
> Haris,
>
> This is not possible on the client.  The protocol that clients use to
> talk to the server (the front end/back end protocol (FE/BE)) only allows
>   one method for getting the results of a sql statement.  It is only
> possible via the FE/BE protocol to get the entire result.  Which is why
> the code works the way it does.  The work Nic is doing works around this
> limitation by issuing multiple sql statements (declare cursor, fetch
> etc.) but it is still the case that the entire result of each statement
> must be read before any additional calls can be made.
>
> What you are pointing out here is the C interface on the server, which
> is not limited by the capabilities of the FE/BE protocol.  But I still
> beleive that even using the server side C interface, cursors are limited
> to a single transaction, thus your original problem still exists.
>
> thanks,
> --Barry
>
> Haris Peco wrote:
> > Hello Barry,
> >   What think You about next idea
> > I have study C interface and it is easy solve my problem without cursor.
> > JDBC load complete qyery result in method QueryExecutor.execute
> > I think that method QueryExecutor.execute work like now except for query
> > (select) - for select this method should do something like PQExec method
> > in C (this is compatible with ResultSet in JDBC specification)
> > When we call ResultSet.next (or like method) we should be initialize
> > fetchSize rows and load rows with method like C method PQgetvalue -
> > consecutive call ResultSet.next should scroll row window (length for
> > window is fetchSize) and if need call new fetchSize rows with method like
> > PQgetvalue
> > 'OutOfmemory' error for large table is not because Postgresql do so than
> > JDBC QueryExecutor.execute method load all rows.
> > If this work in C without cursor then no reason that work in Java
> > I have tried more queries with large table in C and it work fine without
> > cursors.
> >
> > regards
> > Haris Peco
> >
> > On Monday 18 November 2002 05:54 pm, Barry Lind wrote:
> >>Haris,
> >>
> >>I understand your problem.  But unless the database supports cursors
> >>that span transactions, I don't see any solution for you, other than to
> >>issue multiple sql statements to mimic cross transaction queries in your
> >>application.
> >>
> >>--Barry
> >>
> >>Haris Peco wrote:
> >>>On Monday 18 November 2002 05:14 pm, Barry Lind wrote:
> >>>>Nic,
> >>>>
> >>>>Here are my thoughts on this topic.
> >>>>
> >>>>1) Since the server doesn't support cursors across transactions, I
> >>>> don't think the driver should either.  In fact in jdbc3 the
> >>>> DatabaseMetaData object has a supportsResultSetHoldability() method
> >>>> that explicitly lets the driver tell the application what is
> >>>> does/doesn't support in this area.
> >>>>
> >>>>I think running multiple sql statements to mimic this behavior is a
> >>>> very bad idea.  Since the select statements will run at different
> >>>> times they will return different data (since they will pick up
> >>>> commited changes between runs), and if you don't include an order by
> >>>> the results are completely unpredictable.  If someone wants this very
> >>>> unpredictable behavior they can issue the multiple statements
> >>>> themselves.
> >>>>
> >>>>2) I think the use of cursors should be optional.  In fact since most
> >>>>queries don't need them since most queries return a small number of
> >>>> rows , I think the use of cursors needs to be turned on.  I think
> >>>> there should be two ways to do this:  the first is by setting the
> >>>> fetchSize() and the second would be a jdbc url parameter.
> >>>>
> >>>>3) I think the transaction characteristics of the current patch are
> >>>> just fine and conform to the jdbc specification.  The code should
> >>>>automatically close the resultset when a commit occurs.  One thing that
> >>>>will be confusing is that noncursor based result sets will work accross
> >>>>commits, but cursor based ones won't.  But I think that is reasonable.
> >>>
> >>>My problem :
> >>>master-detail
> >>>I select one from many rows master with cursor (big table and only this
> >>>is possible) - In detail I do change and commit (or rollback)
> >>>My select is lost.
> >>>How can I do that ?
> >>>
> >>>>Nic Ferrier wrote:
> >>>>>Message-ID: <87fztyexea.fsf@pooh-sticks-bridge.tapsellferrier.co.uk>
> >>>>>Lines: 24
> >>>>>MIME-Version: 1.0
> >>>>>Content-Type: text/plain; charset=us-ascii
> >>>>>--text follows this line--
> >>>>>
> >>>>>Haris Peco <snpe@snpe.co.yu> writes:
> >>>>>>I have tried.
> >>>>>>DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't
> >>>>>>work
> >>>>>
> >>>>>What error do you get?
> >>>>>
> >>>>>>Prepared command don't work, but my greatest problem are trasnaction
> >>>>>>I hope that cursor in 7.4 will be out of a transaction
> >>>>>
> >>>>>I don't think there's much I can do about the cursor problem.
> >>>>>
> >>>>>>Can Yoy yet another :
> >>>>>>set driver's flag btw jdbc:pgsql:...?cursor=yes
> >>>>>>for use cursor or old way
> >>>>>
> >>>>>What does everyone else think? Is a system doing a different query
> >>>>>each time worth looking into?
> >>>>>
> >>>>>
> >>>>>Nic
> >>>>>
> >>>>>
> >>>>>---------------------------(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)