Re: streaming result sets: progress

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)
>