Re: Complex cursor won't rewind to 0-th row

<typea@l-i-e.com> writes:
> I simply CANNOT get back to the first article -- "International Agreements
> on Nuclear Weapons" no matter what -- I can do all the "move" and "fetch"
> I want, but after first going beyond the 0th row, PostgreSQL insists the
> 0th article is "Iraq's Bomb:  Blueprints and Artifacts" which just ain't
> so.

It didn't work that way for me, but I do see a bug here: reversing
direction after reaching either end of the query result misses the
last or first row, if the top plan node is a UNIQUE.

In general, a lot of complex plans do not work very well in the backward
direction.  UNIQUE seems easy to fix, however.  Attached is a patch for
7.3.

            regards, tom lane


*** src/backend/executor/nodeUnique.c.orig    Thu Jun 20 16:29:28 2002
--- src/backend/executor/nodeUnique.c    Sun Feb  2 14:02:57 2003
***************
*** 58,63 ****
--- 58,68 ----
      /*
       * now loop, returning only non-duplicate tuples. We assume that the
       * tuples arrive in sorted order so we can detect duplicates easily.
+      *
+      * We return the first tuple from each group of duplicates (or the
+      * last tuple of each group, when moving backwards).  At either end
+      * of the subplan, clear priorTuple so that we correctly return the
+      * first/last tuple when reversing direction.
       */
      for (;;)
      {
***************
*** 66,75 ****
           */
          slot = ExecProcNode(outerPlan, (Plan *) node);
          if (TupIsNull(slot))
              return NULL;

          /*
!          * Always return the first tuple from the subplan.
           */
          if (uniquestate->priorTuple == NULL)
              break;
--- 71,86 ----
           */
          slot = ExecProcNode(outerPlan, (Plan *) node);
          if (TupIsNull(slot))
+         {
+             /* end of subplan; reset in case we change direction */
+             if (uniquestate->priorTuple != NULL)
+                 heap_freetuple(uniquestate->priorTuple);
+             uniquestate->priorTuple = NULL;
              return NULL;
+         }

          /*
!          * Always return the first/last tuple from the subplan.
           */
          if (uniquestate->priorTuple == NULL)
              break;