MOVE strangeness
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
"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
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 19073Bruce 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
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 19073Bruce 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
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 19073On 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
"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
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
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 19073Attachments:
/pgpatches/cursortext/plainDownload
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;
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 19073Attachments:
/pgpatches/cursortext/plainDownload
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;
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
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
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