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

<typea(at)l-i-e(dot)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;