Re: move 0 behaviour - Mailing list pgsql-hackers

Dave Cramer wrote:
> Currently there is a TODO list item to have move 0 not position to the
> end of the cursor.
>
> Moving to the end of the cursor is useful, can we keep the behaviour and
> change it to move end, or just leave it the way it is?

I did some research on this.  It turns out the parser uses 0 for ALL, so
when you do a FETCH ALL it is passing zero.  Now, when you do MOVE 0,
you are really asking for FETCH ALL and all the tuples are thrown away
because of the MOVE.

So, that is why MOVE 0 goes to the end of the cursor.  One idea would be
for MOVE 0 to actually move nothing, but jdbc and others need the
ability to move the end of the cursor, perhaps to then back up a certain
amount and read from there.  Seems MOVE 0 is the logical way to do that.
(I can't think of another reasonable value).

I have the following patch which just documents the fact that MOVE 0
goes to the end of the cursor.  It does not change any behavior, just
document it.

If/when I apply the patch, I will remove the TODO item.  Another idea
would be to require MOVE END to move to the end.

Comments?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/move.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/move.sgml,v
retrieving revision 1.13
diff -c -c -r1.13 move.sgml
*** doc/src/sgml/ref/move.sgml    21 Apr 2002 19:02:39 -0000    1.13
--- doc/src/sgml/ref/move.sgml    26 Oct 2002 20:01:15 -0000
***************
*** 37,44 ****
     <command>MOVE</command> allows a user to move cursor position a specified
     number of rows.
     <command>MOVE</command> works like the <command>FETCH</command> command,
!    but only positions the cursor and does
!    not return rows.
    </para>
    <para>
     Refer to
--- 37,44 ----
     <command>MOVE</command> allows a user to move cursor position a specified
     number of rows.
     <command>MOVE</command> works like the <command>FETCH</command> command,
!    but only positions the cursor and does not return rows. The special
!    direction <literal>0</> moves to the end of the cursor.
    </para>
    <para>
     Refer to
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/executor/execMain.c,v
retrieving revision 1.180
diff -c -c -r1.180 execMain.c
*** src/backend/executor/execMain.c    14 Oct 2002 16:51:30 -0000    1.180
--- src/backend/executor/execMain.c    26 Oct 2002 20:01:20 -0000
***************
*** 1119,1125 ****

          /*
           * check our tuple count.. if we've processed the proper number
!          * then quit, else loop again and process more tuples..
           */
          current_tuple_count++;
          if (numberTuples == current_tuple_count)
--- 1119,1127 ----

          /*
           * check our tuple count.. if we've processed the proper number
!          * then quit, else loop again and process more tuples.
!          * If numberTuples is zero, it means we have done MOVE 0
!          * or FETCH ALL and we want to go to the end of the portal.
           */
          current_tuple_count++;
          if (numberTuples == current_tuple_count)
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/tcop/utility.c,v
retrieving revision 1.180
diff -c -c -r1.180 utility.c
*** src/backend/tcop/utility.c    21 Oct 2002 20:31:52 -0000    1.180
--- src/backend/tcop/utility.c    26 Oct 2002 20:01:29 -0000
***************
*** 263,270 ****

                  /*
                   * parser ensures that count is >= 0 and 'fetch ALL' -> 0
                   */
-
                  count = stmt->howMany;
                  PerformPortalFetch(portalName, forward, count,
                                     (stmt->ismove) ? None : dest,
--- 263,270 ----

                  /*
                   * parser ensures that count is >= 0 and 'fetch ALL' -> 0
+                  * MOVE 0 is equivalent to fetch ALL with no returned tuples.
                   */
                  count = stmt->howMany;
                  PerformPortalFetch(portalName, forward, count,
                                     (stmt->ismove) ? None : dest,