Thread: 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 Oddyssey2010 | Oddyssey Two2038 | time_t overflow (3 rows) jtv=> move -3 in c; MOVE 2 jtv=> fetch 3 in c;year | event ------+------------------2010 | A Space Oddyssey2010 | Oddyssey Two2038 | 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 Oddyssey2010 | Oddyssey Two2038 | time_t overflow (3 rows) jtv=> move -2 in c; MOVE 2 jtv=> fetch 3 in c;year | event ------+-----------------2010 | Oddyssey Two2038 | time_t overflow1971 | 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;BEGINjtv=> declare c cursor for select * fromevents;DECLARE CURSORjtv=> 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, Pennsylvania19073
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
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;BEGINjtv=> declare c cursor for select * from events;DECLARE CURSORjtv=>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, Pennsylvania19073
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
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, Pennsylvania19073
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
"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
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 alone2 | delete me3 | 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 alone2 | 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. ServusManfred