Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: typea(at)l-i-e(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Complex cursor won't rewind to 0-th row
Date: 2003-02-02 19:13:12
Message-ID: 14139.1044213192@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
<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;

In response to

Responses

pgsql-bugs by date

Next:From: Josh BerkusDate: 2003-02-02 20:28:53
Subject: Re: Problem when adding an existing primary key
Previous:From: Tom LaneDate: 2003-02-02 03:24:00
Subject: Re: Libpq is not a shared library on Mac OS X

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group