Re: Result Set Cursor Patch - Mailing list pgsql-jdbc

Andy Zeneski wrote:

>> fetchAbsolute() and things that end up calling it doesn't seem to
>> respect the fetchsize, i.e. you always end up with a single-row
>> resultset in memory. How about executing "MOVE ABSOLUTE n; FETCH
>> FORWARD fetchsize" instead of just "FETCH ABSOLUTE n"?
>>
>
> Now that I have code in place for MOVE, this will be simple to implement.

I realized there's an off-by-one error in my example above (I think!) --
as the FETCH will start with the next row after 'n'. Something to watch for.

>> How does the performance of iterating backwards through a resultset
>> compare with the non-cursor case or the forward iteration case? It
>> seems like with the patch it will end up doing a FETCH ABSOLUTE of a
>> single row on each iteration. Really fetchAbsolute needs to do either
>> a "MOVE ABSOLUTE n; FETCH FORWARD fetchsize" or "MOVE ABSOLUTE n;
>> FETCH BACKWARD fetchsize" depending on the resultset's preferred fetch
>> direction (see setFetchDirection)
>>
>
> Okay, now I must ask for some help. In the case that the direction is
> reverse, does that mean that the pointer should position itself at the
> last record at the beginning? What about unknown, should that default to
> forward?

What I mean is that if you've set the fetch direction to backwards, the
driver should probably fetch a block *ending* at the row that it wants
to fetch but doesn't currently have in memory, rather than a block
starting at that row. FETCH BACKWARD is one way of doing this (you'll
need to reverse the order of rows returned though).

i.e. with fetchsize 5 and FETCH_FORWARD we fetch this block:

   10  <= desired row
   11
   12
   13
   14

With FETCH_REVERSE we should instead fetch this block:

   6
   7
   8
   9
   10  <= desired row

After thinking about this a bit it's probably simpler to use a FETCH
FORWARD to get a block of 'fetchsize' rows starting at max(1, row -
fetchsize + 1), i.e. fetch forward from row 6 in the above example.

> Also, when in reverse mode should next() still go forward, or should
> everything be reversed? Meaning, next() would go backwards and
> previous() would go forwards?

setFetchDirection() lets the application provide a hint about the likely
access pattern; the actual meaning of all the resultset positioning
operations are unchanged. it's just there to help the driver load rows
efficiently.

The javadoc says:

> public static final int FETCH_REVERSE
>
> The constant indicating that the rows in a result set will be processed
> in a reverse direction; last-to-first. This constant is used by the
> method setFetchDirection  as a hint to the driver, which the driver may
> ignore.

-O