Re: [HACKERS] MOVE strangeness - Mailing list pgsql-patches

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Sorry, I am not understanding.  If he does:
> > ...
> > here, isn't he sitting at the start of the fourth row, no?
>
> No.  He is sitting *on* the third row.  If he now does FETCH 1, he will
> advance to and return the fourth row; on the other hand, if he does
> FETCH -1, he will back up to and return the second row.
>
> The cursor must be considered to be positioned on its current row, not
> between rows, or the SQL-defined operations UPDATE WHERE CURRENT OF and
> DELETE WHERE CURRENT OF don't make any sense.  (We don't support those
> yet, but we should someday.)
>
> BTW, looking at Date and the SQL spec, I now realize that the recently
> made change to convert FETCH 0 into a no-op is wrong; per spec, FETCH
> RELATIVE 0 means "re-fetch the current row, if any".  By analogy, MOVE 0
> should probably return "MOVE 1" if you are on a real row, "MOVE 0" if
> you are not, corresponding to the number of rows you'd have gotten from
> FETCH 0.  Ugly, but ...

OK, patch attached.  The patch also makes the cursor offset a long from
the parser into the executor.  The tuple counter is already a long in
the executor.

    test=> CREATE TABLE test (x int);
    insert into testCREATE TABLE
    test=> INSERT INTO test VALUES (1);
    INSERT 149758 1
    test=> BEGIN;
    BEGIN
    test=> DECLARE  xx CURSOR FOR SELECT * FROM test;
    DECLARE CURSOR
    test=> MOVE 0 FROM xx;
    MOVE 0
    test=> FETCH 1 FROM xx;
     x
    ---
     1
    (1 row)

    test=> MOVE 0 FROM xx;
    MOVE 1
    test=> FETCH 0 FROM xx;
     x
    ---
     1
    (1 row)

    test=> FETCH 1 FROM xx;
     x
    ---
    (0 rows)

    test=> MOVE 0 FROM xx;
    MOVE 0

--
  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: src/backend/commands/portalcmds.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/commands/portalcmds.c,v
retrieving revision 1.6
diff -c -c -r1.6 portalcmds.c
*** src/backend/commands/portalcmds.c    15 Dec 2002 16:17:42 -0000    1.6
--- src/backend/commands/portalcmds.c    27 Dec 2002 13:52:37 -0000
***************
*** 65,71 ****
  void
  PerformPortalFetch(char *name,
                     bool forward,
!                    int count,
                     CommandDest dest,
                     char *completionTag)
  {
--- 65,71 ----
  void
  PerformPortalFetch(char *name,
                     bool forward,
!                    long count,
                     CommandDest dest,
                     char *completionTag)
  {
***************
*** 100,113 ****
          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
       */
--- 100,147 ----
          return;
      }

!     /* If zero count, handle specially */
      if (count == 0)
!     {
!         bool on_row = false;
!
!         /* Are we sitting on a row? */
!         oldcontext = MemoryContextSwitchTo(PortalGetHeapMemory(portal));
!         queryDesc = PortalGetQueryDesc(portal);
!         estate = queryDesc->estate;
!         if (portal->atStart == false && portal->atEnd == false)
!             on_row = true;
!         MemoryContextSwitchTo(oldcontext);
!
!         if (dest == None)
!         {
!             /* MOVE 0 returns 0/1 based on if FETCH 0 would return a row */
!             if (completionTag && on_row)
!                 strcpy(completionTag, "MOVE 1");
!             return;
!         }
!         else
!         {
!             /* If we are not on a row, FETCH 0 returns nothing */
!             if (!on_row)
!                 return;
!
!             /* Since we are sitting on a row, return the row */
!             /* Back up so we can reread the row */
!             PerformPortalFetch(name, false /* backward */, 1,
!                                None, /* throw away output */
!                                NULL /* do not modify the command tag */);
!
!             /* Set up to fetch one row */
!             count = 1;
!             forward = true;
!         }
!     }

      /* Internally, zero count processes all portal rows */
!     if (count == LONG_MAX)
          count = 0;
!
      /*
       * switch into the portal context
       */
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.388
diff -c -c -r2.388 gram.y
*** src/backend/parser/gram.y    12 Dec 2002 20:35:13 -0000    2.388
--- src/backend/parser/gram.y    27 Dec 2002 13:52:55 -0000
***************
*** 2731,2738 ****
  fetch_how_many:
              Iconst                                    { $$ = $1; }
              | '-' Iconst                            { $$ = - $2; }
!             | ALL                                    { $$ = INT_MAX; }
!             | LAST                                    { $$ = INT_MAX; }
              | NEXT                                    { $$ = 1; }
              | PRIOR                                    { $$ = -1; }
          ;
--- 2731,2738 ----
  fetch_how_many:
              Iconst                                    { $$ = $1; }
              | '-' Iconst                            { $$ = - $2; }
!             | ALL                                    { $$ = LONG_MAX; }
!             | LAST                                    { $$ = LONG_MAX; }
              | NEXT                                    { $$ = 1; }
              | PRIOR                                    { $$ = -1; }
          ;
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/tcop/utility.c,v
retrieving revision 1.185
diff -c -c -r1.185 utility.c
*** src/backend/tcop/utility.c    6 Dec 2002 05:00:31 -0000    1.185
--- src/backend/tcop/utility.c    27 Dec 2002 13:53:01 -0000
***************
*** 257,263 ****
                  FetchStmt  *stmt = (FetchStmt *) parsetree;
                  char       *portalName = stmt->portalname;
                  bool        forward;
!                 int            count;

                  forward = (bool) (stmt->direction == FORWARD);

--- 257,263 ----
                  FetchStmt  *stmt = (FetchStmt *) parsetree;
                  char       *portalName = stmt->portalname;
                  bool        forward;
!                 long        count;

                  forward = (bool) (stmt->direction == FORWARD);

Index: src/include/commands/portalcmds.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/commands/portalcmds.h,v
retrieving revision 1.3
diff -c -c -r1.3 portalcmds.h
*** src/include/commands/portalcmds.h    13 Nov 2002 00:44:09 -0000    1.3
--- src/include/commands/portalcmds.h    27 Dec 2002 13:53:05 -0000
***************
*** 25,31 ****
   *        BadArg if forward invalid.
   *        "ERROR" if portal not found.
   */
! extern void PerformPortalFetch(char *name, bool forward, int count,
                     CommandDest dest, char *completionTag);

  /*
--- 25,31 ----
   *        BadArg if forward invalid.
   *        "ERROR" if portal not found.
   */
! extern void PerformPortalFetch(char *name, bool forward, long count,
                     CommandDest dest, char *completionTag);

  /*
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/nodes/parsenodes.h,v
retrieving revision 1.223
diff -c -c -r1.223 parsenodes.h
*** src/include/nodes/parsenodes.h    12 Dec 2002 20:35:16 -0000    1.223
--- src/include/nodes/parsenodes.h    27 Dec 2002 13:53:11 -0000
***************
*** 1198,1204 ****
  {
      NodeTag        type;
      int            direction;        /* FORWARD or BACKWARD */
!     int            howMany;        /* amount to fetch */
      char       *portalname;        /* name of portal (cursor) */
      bool        ismove;            /* TRUE if MOVE */
  } FetchStmt;
--- 1198,1204 ----
  {
      NodeTag        type;
      int            direction;        /* FORWARD or BACKWARD */
!     long        howMany;        /* amount to fetch */
      char       *portalname;        /* name of portal (cursor) */
      bool        ismove;            /* TRUE if MOVE */
  } FetchStmt;