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