Re: move 0 behaviour - Mailing list pgsql-hackers

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > 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).
>
> It would seem more logical and reasonable for MOVE 0 to do nothing and
> have some special syntax such as MOVE LAST to move to the end.  (MOVE LAST
> would actually be consistent with the standard syntax FETCH LAST.)

Yea, I started thinking and we need to get MOVE/FETCH to make sense.
The following patch makes FETCH/MOVE 0 do nothing, and FETCH LAST move
to the end.  I was going to use the word END, but if LAST is more
standard, we will use that.  It uses INT_MAX in the grammar for FETCH
ALL/MOVE LAST, but maps that to zero so it is consistent in the
/executor code.

I will keep this patch for 7.4.

JDBC folks, I realize you need this.  Seems you will have to use MOVE 0
for 7,3 and MOVE LAST for 7.4.

--
  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    31 Oct 2002 01:15:42 -0000
***************
*** 21,27 ****
     <date>1999-07-20</date>
    </refsynopsisdivinfo>
    <synopsis>
! MOVE [ <replaceable class="PARAMETER">direction</replaceable> ] [ <replaceable class="PARAMETER">count</replaceable>
] 
      { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable>
    </synopsis>
   </refsynopsisdiv>
--- 21,28 ----
     <date>1999-07-20</date>
    </refsynopsisdivinfo>
    <synopsis>
! MOVE [ <replaceable class="PARAMETER">direction</replaceable> ]
!     {<replaceable class="PARAMETER">count</replaceable> | LAST }
      { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable>
    </synopsis>
   </refsynopsisdiv>
Index: src/backend/commands/portalcmds.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/commands/portalcmds.c,v
retrieving revision 1.3
diff -c -c -r1.3 portalcmds.c
*** src/backend/commands/portalcmds.c    4 Sep 2002 20:31:15 -0000    1.3
--- src/backend/commands/portalcmds.c    31 Oct 2002 01:15:44 -0000
***************
*** 15,20 ****
--- 15,22 ----

  #include "postgres.h"

+ #include <limits.h>
+
  #include "commands/portalcmds.h"
  #include "executor/executor.h"

***************
*** 55,61 ****
   *
   *    name: name of portal
   *    forward: forward or backward fetch?
!  *    count: # of tuples to fetch (0 implies all)
   *    dest: where to send results
   *    completionTag: points to a buffer of size COMPLETION_TAG_BUFSIZE
   *        in which to store a command completion status string.
--- 57,63 ----
   *
   *    name: name of portal
   *    forward: forward or backward fetch?
!  *    count: # of tuples to fetch
   *    dest: where to send results
   *    completionTag: points to a buffer of size COMPLETION_TAG_BUFSIZE
   *        in which to store a command completion status string.
***************
*** 100,105 ****
--- 102,115 ----
          return;
      }

+     /* If zero count, we are done */
+     if (count == 0)
+         return;
+
+     /* Internally, zero count processes all portal rows */
+     if (count == INT_MAX)
+         count = 0;
+
      /*
       * switch into the portal context
       */
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    31 Oct 2002 01:15:50 -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,1126 ----

          /*
           * check our tuple count.. if we've processed the proper number
!          * then quit, else loop again and process more tuples.  Zero
!          * number_tuples means no limit.
           */
          current_tuple_count++;
          if (numberTuples == current_tuple_count)
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.370
diff -c -c -r2.370 gram.y
*** src/backend/parser/gram.y    22 Sep 2002 21:44:43 -0000    2.370
--- src/backend/parser/gram.y    31 Oct 2002 01:16:14 -0000
***************
*** 49,54 ****
--- 49,55 ----
  #include "postgres.h"

  #include <ctype.h>
+ #include <limits.h>

  #include "access/htup.h"
  #include "catalog/index.h"
***************
*** 357,363 ****
      JOIN
      KEY

!     LANCOMPILER LANGUAGE LEADING LEFT LEVEL LIKE LIMIT
      LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
      LOCK_P

--- 358,364 ----
      JOIN
      KEY

!     LANCOMPILER LANGUAGE LAST LEADING LEFT LEVEL LIKE LIMIT
      LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
      LOCK_P

***************
*** 2644,2650 ****
                      if ($3 < 0)
                      {
                          $3 = -$3;
!                         $2 = (($2 == FORWARD)? BACKWARD: FORWARD);
                      }
                      n->direction = $2;
                      n->howMany = $3;
--- 2645,2651 ----
                      if ($3 < 0)
                      {
                          $3 = -$3;
!                         $2 = (($2 == FORWARD) ? BACKWARD: FORWARD);
                      }
                      n->direction = $2;
                      n->howMany = $3;
***************
*** 2712,2719 ****
  fetch_how_many:
              Iconst                                    { $$ = $1; }
              | '-' Iconst                            { $$ = - $2; }
!                                             /* 0 means fetch all tuples*/
!             | ALL                                    { $$ = 0; }
              | NEXT                                    { $$ = 1; }
              | PRIOR                                    { $$ = -1; }
          ;
--- 2713,2720 ----
  fetch_how_many:
              Iconst                                    { $$ = $1; }
              | '-' Iconst                            { $$ = - $2; }
!             | ALL                                    { $$ = INT_MAX; }
!             | LAST                                    { $$ = INT_MAX; }
              | NEXT                                    { $$ = 1; }
              | PRIOR                                    { $$ = -1; }
          ;
***************
*** 7098,7103 ****
--- 7099,7105 ----
              | KEY
              | LANGUAGE
              | LANCOMPILER
+             | LAST
              | LEVEL
              | LISTEN
              | LOAD
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/parser/keywords.c,v
retrieving revision 1.127
diff -c -c -r1.127 keywords.c
*** src/backend/parser/keywords.c    18 Sep 2002 21:35:22 -0000    1.127
--- src/backend/parser/keywords.c    31 Oct 2002 01:16:15 -0000
***************
*** 172,177 ****
--- 172,178 ----
      {"key", KEY},
      {"lancompiler", LANCOMPILER},
      {"language", LANGUAGE},
+     {"last", LAST},
      {"leading", LEADING},
      {"left", LEFT},
      {"level", LEVEL},
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    31 Oct 2002 01:16:18 -0000
***************
*** 262,270 ****
                  forward = (bool) (stmt->direction == FORWARD);

                  /*
!                  * parser ensures that count is >= 0 and 'fetch ALL' -> 0
                   */
-
                  count = stmt->howMany;
                  PerformPortalFetch(portalName, forward, count,
                                     (stmt->ismove) ? None : dest,
--- 262,269 ----
                  forward = (bool) (stmt->direction == FORWARD);

                  /*
!                  * parser ensures that count is >= 0
                   */
                  count = stmt->howMany;
                  PerformPortalFetch(portalName, forward, count,
                                     (stmt->ismove) ? None : dest,