Here's something that's been bothering me for a while...  Perhaps this
is correct behaviour, but I can't quite see how.  This does not happen
if I replace the FETCHes by MOVEs.

Here's the reference case:

jtv=> begin;
BEGIN
jtv=> declare c cursor for select * from events;
DECLARE CURSOR
jtv=> fetch 3 in c;
 year |      event       
------+------------------
 2010 | A Space Oddyssey
 2010 | Oddyssey Two
 2038 | time_t overflow
(3 rows)

jtv=> move -3 in c;
MOVE 2
jtv=> fetch 3 in c;
 year |      event       
------+------------------
 2010 | A Space Oddyssey
 2010 | Oddyssey Two
 2038 | time_t overflow
(3 rows)

jtv=> end;
COMMIT


Okay, since that "move -3" claims to have moved only 2 rows backwards, I
tried the same but moving backwards by only 2 rows.  This gives me the
same response for the MOVE, but my cursor evidently doesn't end up in
the same place:


jtv=> begin;
BEGIN
jtv=> declare c cursor for select * from events;
DECLARE CURSOR
jtv=> fetch 3 in c;
 year |      event       
------+------------------
 2010 | A Space Oddyssey
 2010 | Oddyssey Two
 2038 | time_t overflow
(3 rows)

jtv=> move -2 in c;
MOVE 2
jtv=> fetch 3 in c;
 year |      event      
------+-----------------
 2010 | Oddyssey Two
 2038 | time_t overflow
 1971 | jtv
(3 rows)

jtv=> end;
COMMIT


This makes it a bit hard for me to figure out just how far I moved my
cursor backwards!  Moving by BACKWARD ALL will give me the same result
as moving by -3.

Is this behaviour intentional?  If not, can it be fixed?


Jeroen



Thu, 26 Dec 2002 02:44:30 +0100
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
MOVE strangeness
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> Here's something that's been bothering me for a while...  Perhaps this
> is correct behaviour, but I can't quite see how.

It looks fine to me, given the underlying model of how a cursor works,
which probably isn't really written down anywhere :-(.  Briefly:

1. A cursor can be positioned before the first row, after the last row,
or on any individual row of its SELECT result.  The initial state is
before the first row.

2. "FETCH 1" advances the cursor one row (if possible), and if it's not
now after the last row, returns the row the cursor is now on.  "FETCH n"
repeats this operation n times.

3. "FETCH BACKWARD 1" (or FETCH -1) moves the cursor back one row (if
possible), and if it's not now before the first row, returns the row the
cursor is now on.  "FETCH -n" repeats this operation n times.

4. The result count is the number of rows successfully returned (or for
MOVE, the number that would have been returned by the equivalent FETCH).


> This makes it a bit hard for me to figure out just how far I moved my
> cursor backwards!  Moving by BACKWARD ALL will give me the same result
> as moving by -3.

If the return count is not the same as abs(n), then you ran off the end
of the result, and are now positioned before the start or after the end
depending on the requested direction.  If the return count is the same
as abs(n), then you are positioned on a real row.  This doesn't seem any
more confusing to me than any other convention that might have been
picked.

> This does not happen
> if I replace the FETCHes by MOVEs.

I'm a little confused by that remark; it seems to me that FETCH and MOVE
have identical behaviors so far as repositioning the cursor is concerned.
(Internally, MOVE *is* a FETCH, it just suppresses output of the rows.)
Can you give an example where you get different behavior?

			regards, tom lane

Thu, 26 Dec 2002 13:26:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: MOVE strangeness 
Tom Lane wrote:
> > This does not happen
> > if I replace the FETCHes by MOVEs.
> 
> I'm a little confused by that remark; it seems to me that FETCH and MOVE
> have identical behaviors so far as repositioning the cursor is concerned.
> (Internally, MOVE *is* a FETCH, it just suppresses output of the rows.)
> Can you give an example where you get different behavior?

I think I see what Jeroen is saying.  In this example he posted:
	
	jtv=> begin;
	BEGIN
	jtv=> declare c cursor for select * from events;
	DECLARE CURSOR
	jtv=> fetch 3 in c;
	 year |      event       
	------+------------------
	 2010 | A Space Oddyssey
	 2010 | Oddyssey Two
	 2038 | time_t overflow
	(3 rows)
	
	jtv=> move -3 in c;
	MOVE 2

Why does the MOVE -3 return 2?  If he has fetched 3, he is at the end of
the third row.  If he does MOVE -3, hasn't he moved backward three rows
to the start of the first row?

-- 
  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

Thu, 26 Dec 2002 13:41:52 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: MOVE strangeness
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Why does the MOVE -3 return 2?

Because he's successfully backed up over 2 real rows.  Had he done FETCH
-3 in the same situation, he'd have gotten back 2 rows; there is no
third row it could have returned, so it's hard to argue that the count
should be anything but 2.  (If you think it should be 3, what if I say
MOVE -10000?  Should I get back a count of 10000?)

> If he has fetched 3, he is at the end of
> the third row.  If he does MOVE -3, hasn't he moved backward three rows
> to the start of the first row?

There is no "end of a row" as distinct from "start of a row".  You can
be on a row, or before the first row, or after the last row.  There is
no other state besides that.

			regards, tom lane

Thu, 26 Dec 2002 13:58:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: MOVE strangeness 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Why does the MOVE -3 return 2?
> 
> Because he's successfully backed up over 2 real rows.  Had he done FETCH
> -3 in the same situation, he'd have gotten back 2 rows; there is no
> third row it could have returned, so it's hard to argue that the count
> should be anything but 2.  (If you think it should be 3, what if I say
> MOVE -10000?  Should I get back a count of 10000?)

Sorry, I am not understanding.  If he does:
	
	jtv=> begin;
	BEGIN
	jtv=> declare c cursor for select * from events;
	DECLARE CURSOR
	jtv=> fetch 3 in c;
	 year |      event       
	------+------------------
	 2010 | A Space Oddyssey
	 2010 | Oddyssey Two
	 2038 | time_t overflow
	(3 rows)

here, isn't he sitting at the start of the fourth row, no?  When he does
MOVE -3, doesn't he move from the start of the 4th row to the start of
the 1st row?
	
	jtv=> move -3 in c;
	MOVE 2

> > If he has fetched 3, he is at the end of
> > the third row.  If he does MOVE -3, hasn't he moved backward three rows
> > to the start of the first row?
> 
> There is no "end of a row" as distinct from "start of a row".  You can
> be on a row, or before the first row, or after the last row.  There is
> no other state besides that.

So, you are saying if he does a FETCH 3, he is still sitting on the 3rd
row, rather than at the gap between the 3rd and 4th rows?  If so, the
behavior does make sense.

-- 
  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

Thu, 26 Dec 2002 14:04:07 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: MOVE strangeness
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 ...

			regards, tom lane

Thu, 26 Dec 2002 14:14:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: MOVE strangeness 
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.

OK, and it makes sense FETCH -1 will move back a row rather than
re-reading the 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.)

Yes, that's where the positioning makes sense.

> 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, I will fix those.  I am working on it now.  I think I am going to
have to break the internal representation that a zero fetch means fetch
all.  Right now, we use INT_MAX for fetch all in PerformPortalFetch.

-- 
  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

Thu, 26 Dec 2002 17:24:17 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: MOVE strangeness
On Thu, Dec 26, 2002 at 02:14:40PM -0500, 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.                       
                                                                                
So we're talking about pre-increment and pre-decrement, and a cursor            
starting at position 0 in a 1-based array (or -1 in a 0-based one)?             
                                                                                
                                                                                
> 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 ...                                                       
                                                                                
Okay, given that, is there really any reason why MOVE should return the         
number of rows that would have been fetched?  Why not report the number         
of rows moved?  Having two different MOVE commands from the same starting       
positions yield indistinguishable results yet bring you to different            
states is very, very awkward and possibly quite useless.  Better yet,           
why should FETCH report the number of rows fetched instead of the               
number of rows moved when you're going to extract the former from the           
PQresult's PQntuples() anyway?  The current status string is completely         
redundant in that case.                                                         
                                                                                
If the status string for MOVE is both inconclusive (as my case shows)           
and inconsistent (the MOVE 0 case) now, but it's linked to the status           
string for FETCH which is redundant, it seems logical to give them              
new, consistent semantics that are useful and intuitive.  Just report           
the number of rows _moved_ and we can all go back to lead rich,                 
meaningful lives.  I for one will also sleep better knowing that the            
number of rows reported is based on the same counting system as the             
number of rows requested in the SQL command that the status string is           
supposed to echo.  If FETCH 0 expects to fetch 1 row, why not have a            
result set of 1 row and a result string that echoes the command?                
                                                                                
Which begs the question: is there anything special about the one-before-        
first row or can a cursor be moved to any arbitrary point outside its           
actual set of rows?  Oh, what odious web we weave / when first we               
practice to iteratewithoutthebenefitsofzero-basedarithmeticandhalf-             
openintervals.                                                                  
                                                                                
                                                                                
Jeroen


Fri, 27 Dec 2002 05:15:47 +0100
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: MOVE strangeness
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> Okay, given that, is there really any reason why MOVE should return the
> number of rows that would have been fetched?  Why not report the number
> of rows moved?  Having two different MOVE commands from the same starting
> positions yield indistinguishable results yet bring you to different
> states is very, very awkward and possibly quite useless.  Better yet,
> why should FETCH report the number of rows fetched instead of the
> number of rows moved when you're going to extract the former from the
> PQresult's PQntuples() anyway?

The main reason why neither of these are likely to change is that it
will break existing, working applications if we change it.  "Why not"
is not an argument that will win out against that point ... especially
not when it's debatable whether the proposed change is actually an
improvement.

			regards, tom lane

Fri, 27 Dec 2002 00:18:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: MOVE strangeness 
Tom Lane wrote:
> 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.)

Okay.  But then doesn't it make sense for FETCH to fetch the contents
of the row (and subsequent requested rows) that the cursor is
currently on *then* move, and not the other way around?  Were that the
model, then the only situation in which the cursor would not be on a
row is if it's past the last one (or if it pointed to an empty set).
And the initial declaration of the cursor would thus normally place
the cursor on the first row fetched, just as you'd expect.

"Fetch 3" would fetch whatever row the cursor is currently on and the
following 2 rows, then move the cursor down 3 rows (so that it's on
the row that follows the last row fetched).

Additionally, with that model, UPDATE WHERE CURRENT OF would work
exactly as you'd expect in all situations: it would update the row the
cursor is on or, if the cursor is beyond the last row, would fail.
MOVE would also work exactly as you'd expect: MOVE -3 would move the
cursor back 3 positions, or to the beginning of the set, whichever
comes first, and would report how many positions it was able to move.


My read of the spec indicates that cursors don't behave the way I
describe above.  If I'm right, then does anyone here know why the spec
calls for something different?


-- 
Kevin Brown					      kevin@sysexperts.com

Fri, 27 Dec 2002 00:04:36 -0800
Kevin Brown <kevin@sysexperts.com>
Re: MOVE strangeness
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;
Fri, 27 Dec 2002 09:03:20 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] MOVE strangeness
Bruce Momjian wrote:
> 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.

Here is an updated patch with doc changes.

-- 
  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/fetch.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/fetch.sgml,v
retrieving revision 1.21
diff -c -c -r1.21 fetch.sgml
*** doc/src/sgml/ref/fetch.sgml	21 Apr 2002 19:02:39 -0000	1.21
--- doc/src/sgml/ref/fetch.sgml	27 Dec 2002 14:46:03 -0000
***************
*** 89,95 ****
  	   <para>
  	    A signed integer that specifies how many rows to fetch.
  	    Note that a negative integer is equivalent to changing the sense of
! 	    FORWARD and BACKWARD.
  	   </para>
  	  </listitem>
  	 </varlistentry>
--- 89,95 ----
  	   <para>
  	    A signed integer that specifies how many rows to fetch.
  	    Note that a negative integer is equivalent to changing the sense of
! 	    FORWARD and BACKWARD. Zero re-fetches the current row.
  	   </para>
  	  </listitem>
  	 </varlistentry>
***************
*** 176,205 ****
         <para>
  	<productname>PostgreSQL</productname> does not support absolute
  	positioning of cursors.
-        </para>
-       </listitem>
-      </varlistentry>
- 
-      <varlistentry>
-       <term><computeroutput>
- ERROR:  FETCH/RELATIVE at current position is not supported
-        </computeroutput></term>
-       <listitem>
-        <para>
- 	<acronym>SQL92</acronym> allows one to repetitively retrieve the cursor
- 	at its <quote>current position</quote> using the syntax
- 	<synopsis>
- FETCH RELATIVE 0 FROM <replaceable class="PARAMETER">cursor</replaceable>.
- 	</synopsis>
-        </para>
- 
-        <para>
- 	<productname>PostgreSQL</productname> does not currently support
- 	this notion; in fact the value zero is reserved to indicate that
- 	all rows should be retrieved and is equivalent to specifying the ALL keyword.
- 	If the RELATIVE keyword has been used, <productname>PostgreSQL</productname> 
- 	assumes that the user intended <acronym>SQL92</acronym> behavior
- 	and returns this error message.
         </para>
        </listitem>
       </varlistentry>
--- 176,181 ----
Index: doc/src/sgml/ref/move.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/move.sgml,v
retrieving revision 1.14
diff -c -c -r1.14 move.sgml
*** doc/src/sgml/ref/move.sgml	13 Nov 2002 00:44:08 -0000	1.14
--- doc/src/sgml/ref/move.sgml	27 Dec 2002 14:46:03 -0000
***************
*** 35,42 ****
     Description
    </title>
    <para>
!    <command>MOVE</command> allows a user to move cursor position a specified
!    number of rows.
     <command>MOVE</command> works like the <command>FETCH</command> command,
     but only positions the cursor and does not return rows.
     <replaceable class="PARAMETER">LAST</replaceable> moves to the end
--- 35,42 ----
     Description
    </title>
    <para>
!    <command>MOVE</command> allows a user to move the cursor position a 
!    specified number of rows.
     <command>MOVE</command> works like the <command>FETCH</command> command,
     but only positions the cursor and does not return rows.
     <replaceable class="PARAMETER">LAST</replaceable> moves to the end
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 14:46:04 -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 14:46:15 -0000
***************
*** 2591,2603 ****
  FetchStmt:	FETCH direction fetch_how_many from_in name
  				{
  					FetchStmt *n = makeNode(FetchStmt);
- 					if ($2 == RELATIVE)
- 					{
- 						if ($3 == 0)
- 							elog(ERROR,
- 					"FETCH / RELATIVE at current position is not supported");
- 						$2 = FORWARD;
- 					}
  					if ($3 < 0)
  					{
  						$3 = -$3;
--- 2591,2596 ----
***************
*** 2629,2638 ****
  			| FETCH direction from_in name
  				{
  					FetchStmt *n = makeNode(FetchStmt);
- 					if ($2 == RELATIVE)
- 					{
- 						$2 = FORWARD;
- 					}
  					n->direction = $2;
  					n->howMany = 1;
  					n->portalname = $4;
--- 2622,2627 ----
***************
*** 2719,2738 ****
  
  direction:	FORWARD									{ $$ = FORWARD; }
  			| BACKWARD								{ $$ = BACKWARD; }
! 			| RELATIVE								{ $$ = RELATIVE; }
  			| ABSOLUTE
  				{
  					elog(NOTICE,
  					"FETCH / ABSOLUTE not supported, using RELATIVE");
! 					$$ = RELATIVE;
  				}
  		;
  
  fetch_how_many:
  			Iconst									{ $$ = $1; }
  			| '-' Iconst							{ $$ = - $2; }
! 			| ALL									{ $$ = INT_MAX; }
! 			| LAST									{ $$ = INT_MAX; }
  			| NEXT									{ $$ = 1; }
  			| PRIOR									{ $$ = -1; }
  		;
--- 2708,2727 ----
  
  direction:	FORWARD									{ $$ = FORWARD; }
  			| BACKWARD								{ $$ = BACKWARD; }
! 			| RELATIVE								{ $$ = FORWARD; }
  			| ABSOLUTE
  				{
  					elog(NOTICE,
  					"FETCH / ABSOLUTE not supported, using RELATIVE");
! 					$$ = FORWARD;
  				}
  		;
  
  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 14:46:16 -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 14:46:17 -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 14:46:23 -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;
Fri, 27 Dec 2002 09:48:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] MOVE strangeness
Kevin Brown <kevin@sysexperts.com> writes:
> Tom Lane wrote:
>> 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.)

> Okay.  But then doesn't it make sense for FETCH to fetch the contents
> of the row (and subsequent requested rows) that the cursor is
> currently on *then* move, and not the other way around?

No, because WHERE CURRENT OF operates on the row last returned by FETCH,
according to the spec.  AFAICT, the conceptual model I described a
couple messages back corresponds directly to a subset of the SQL92
specification for cursors: "before first row", "on some row", and "after
last row" are all states described by the spec.  (It is a subset because
we don't support DELETE WHERE CURRENT OF --- if we did, we'd need
additional states to handle the situation where the cursor is pointing
at a just-deleted row.)

We don't get to define our own behavior for FETCH.

> Additionally, with that model, UPDATE WHERE CURRENT OF would work
> exactly as you'd expect in all situations: it would update the row the
> cursor is on or, if the cursor is beyond the last row, would fail.

How do you figure that?  It would operate on the row after the last one
you'd seen, which seems pretty darn bizarre (ie, useless and dangerous)
to me.

			regards, tom lane

Fri, 27 Dec 2002 12:26:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: MOVE strangeness 
Tom Lane wrote:
> > Okay.  But then doesn't it make sense for FETCH to fetch the contents
> > of the row (and subsequent requested rows) that the cursor is
> > currently on *then* move, and not the other way around?
> 
> No, because WHERE CURRENT OF operates on the row last returned by FETCH,
> according to the spec.  AFAICT, the conceptual model I described a
> couple messages back corresponds directly to a subset of the SQL92
> specification for cursors: "before first row", "on some row", and "after
> last row" are all states described by the spec.  (It is a subset because
> we don't support DELETE WHERE CURRENT OF --- if we did, we'd need
> additional states to handle the situation where the cursor is pointing
> at a just-deleted row.)
> 
> We don't get to define our own behavior for FETCH.

I know.  I just think it's unfortunate that the spec was written the
way it was, because the behavior the spec calls for is not what I
think most people would expect of a cursor.

When I think of a cursor's behavior, what I would normally expect is
for it to behave the way my editor's cursor behaves.  The cursor is
either on a character or on the position after the last character.
When I type in insert mode, the characters I type are inserted before
the character the cursor is on.  When I type in overstrike ("update")
mode, the character the cursor is on is replaced by the character I
typed and the cursor is moved forward one position.  When I delete the
character the cursor is on (using the DEL key), the character the
cursor is on is deleted and the character that followed now becomes
the character the cursor sits on.  When I backspace, the character
before the cursor is deleted and the cursor remains on the same
character it was on prior to the backspace operation.

Some editors use a very thin cursor that is always placed between
characters, but the resulting semantics are basically the same as
described above.

This model is extremely common and, IMO, sensible.  If SQL cursors
were implemented with the same semantics, then UPDATE WHERE CURRENT OF
would update the row the cursor currently sits on, then (perhaps) move
the cursor forward, just as you'd expect (since it's the equivalent of
typing a character in overstrike mode).  INSERT WHERE CURRENT OF would
insert a new row before the row the cursor is on (so to insert a row
at the end you'd have to move the cursor past the last row) and the
cursor would remain positioned on the row it was on.  DELETE WHERE
CURRENT OF would, of course, delete the number of rows requested,
starting with the row the cursor is on, and position the first
remaining row after the deleted set underneath the cursor.  There
would be no "cursor points to the deleted row" because such a thing
makes no sense and doesn't have any real use that I'm aware of
(correct me if I'm wrong, please).

There's no need to have a "before the first row" position *and* an
"after the last row" position, since INSERT must always insert either
before the current row or after the current row (before if you use the
same semantics I described above).  


None of this matters, of course, because the SQL spec calls for a
completely different behavior.

My question is: *why* does it call for such a completely different
behavior?  Does anyone here have any insight into that?



-- 
Kevin Brown					      kevin@sysexperts.com

Sun, 29 Dec 2002 16:39:37 -0800
Kevin Brown <kevin@sysexperts.com>
Re: MOVE strangeness
On Sun, 29 Dec 2002 16:39:37 -0800, Kevin Brown <kevin@sysexperts.com>
wrote:
>> > Okay.  But then doesn't it make sense for FETCH to fetch the contents
>> > of the row (and subsequent requested rows) that the cursor is
>> > currently on *then* move, and not the other way around?

>This model is extremely common and, IMO, sensible.  

OTOH, seeing a row *before* updating or deleting it is *extremely*
useful.  Here is what you are proposing:

SELECT * FROM t ORDER BY id;
id | txt
---+---------------
 1 | leave me alone
 2 | delete me
 3 | keep me

BEGIN;
DECLARE c CURSOR FOR SELECT * FROM t ORDER BY id;
FETCH 1 FROM c;
id | txt
---+---------------
 1 | leave me alone

-- ok, leave it alone ...

FETCH 1 FROM c;
id | txt
---+---------------
 2 | delete me

-- got it!  (really??)
DELETE FROM t WHERE CURRENT OF c;
COMMIT;

SELECT * FROM t ORDER BY id;
id | txt
---+---------------
 1 | leave me alone
 2 | delete me

-- oops!!

> just as you'd expect

No, not me.  Would *you* expect this?  If you really want to draw an
analogy between databases and editors, compare rows to lines, not
characters.  Now imagine an editor window just one line high, then it
is clear that after a row is brought into the window you don't want
the cursor to silently advance to the next (invisible) row.

Servus
 Manfred

Mon, 30 Dec 2002 19:50:52 +0100
Manfred Koizar <mkoi-pg@aon.at>
Re: MOVE strangeness