Re: not fetching all query results

On 7/19/2012 10:16 PM, Craig Ringer wrote:
> I haven't looked into how the fetch size code works to see whether it's
> possible for it to operate in autocommit.
>
> given that and the subsequent code I don't understand why an explicit
> transaction is required. I've only done a tiny bit with the JDBC code,
> though, and only related to the authentication system and client
> certificates.

A transaction is required to keep the portal (protocol level cursor) 
open.  In autocommit mode, each batch fetched runs in its own 
transaction.  So after the first batch is fetched, the autocommit 
transaction ends and the cursor is destroyed.  So the next batch fetch 
cannot work.

For multiple fetches to work in autocommit mode, you need a WITH HOLD 
cursor.  This has a performance impact and you cannot create a WITH HOLD 
portal at the protocol level.  So making this work is decidedly 
non-trivial.  You would need to rework the query processor to rewrite 
the SQL to be a CREATE CURSOR statement.

A similar problem occurs when trying to make fetchsize work for 
scrollable resultsets.  You can't create a scrollable portal at the 
protocol level and you cannot move any direction other than forwards at 
the protocol level.

So the JDBC team has been sort of waiting for the frontend/backend 
protocol to support these features before supporting them in the drive.

The one thing that could be fixed is making refcursors respect fetchsize 
with the same restrictions as the current normal query process.

Kris Jurka